Data Type ConversionΒΆ

Let us understand how we can type cast to change the data type of extracted value to its original type.

%load_ext sql
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%%sql

SELECT '09'::int
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
int4
9
%%sql

SELECT current_date AS current_date
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
current_date
2020-12-01
%%sql

SELECT split_part('2020-09-30', '-', 2) AS month
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
month
09
%%sql

SELECT split_part('2020-09-30', '-', 2)::int AS month
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
month
9
%%sql

SELECT to_char('2020-09-30'::date, 'MM') AS month
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
month
09
%%sql

SELECT to_char('2020-09-30'::date, 'MM')::int AS month
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
month
9
%%sql

SELECT to_char(current_date, 'MM')::int AS month
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
month
12
%%sql

SELECT cast('0.04000' AS FLOAT) AS result
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
0.04
%%sql

SELECT '0.04000'::float AS result
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
0.04
%%sql

SELECT cast('09' AS INT) AS result
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
9
%%sql

SELECT '09'::int AS result
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
9