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 |