Data Type Conversion¶
Let us understand how we can type cast to change the data type of extracted value to its original type.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%%sql
SELECT '09'::int
1 rows affected.
Out[3]:
| int4 |
|---|
| 9 |
In [4]:
%%sql
SELECT current_date AS current_date
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[4]:
| current_date |
|---|
| 2022-03-14 |
In [5]:
%%sql
SELECT split_part('2020-09-30', '-', 2) AS month
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[5]:
| month |
|---|
| 09 |
In [6]:
%%sql
SELECT split_part('2020-09-30', '-', 2)::int AS month
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[6]:
| month |
|---|
| 9 |
In [7]:
%%sql
SELECT to_char('2020-09-30'::date, 'MM') AS month
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[7]:
| month |
|---|
| 09 |
In [8]:
%%sql
SELECT to_char('2020-09-30'::date, 'MM')::int AS month
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[8]:
| month |
|---|
| 9 |
In [9]:
%%sql
SELECT to_char(current_date, 'MM')::int AS month
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[9]:
| month |
|---|
| 3 |
In [10]:
%%sql
SELECT cast('0.04000' AS FLOAT) AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[10]:
| result |
|---|
| 0.04 |
In [11]:
%%sql
SELECT '0.04000'::float AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[11]:
| result |
|---|
| 0.04 |
In [12]:
%%sql
SELECT cast('09' AS INT) AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[12]:
| result |
|---|
| 9 |
In [13]:
%%sql
SELECT '09'::int AS result
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[13]:
| result |
|---|
| 9 |