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

Copy to clipboard

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%%sql

SELECT '09'::int

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

int4
9
%%sql

SELECT current_date AS current_date

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

current_date
2020-12-01
%%sql

SELECT split_part('2020-09-30', '-', 2) AS month

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

month
09
%%sql

SELECT split_part('2020-09-30', '-', 2)::int AS month

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

month
9
%%sql

SELECT to_char('2020-09-30'::date, 'MM') AS month

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

month
09
%%sql

SELECT to_char('2020-09-30'::date, 'MM')::int AS month

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

month
9
%%sql

SELECT to_char(current_date, 'MM')::int AS month

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

month
12
%%sql

SELECT cast('0.04000' AS FLOAT) AS result

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

result
0.04
%%sql

SELECT '0.04000'::float AS result

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

result
0.04
%%sql

SELECT cast('09' AS INT) AS result

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

result
9
%%sql

SELECT '09'::int AS result

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

result
9