Casting of Data Types

Home
»
Casting of Data Types

On July 18, 2021
By admin
In Uncategorized
No comments

admin

July 16, 2021

Share post:

PostgreSQL has more than one way of forcing the casting of one data type to another.

Casting Method	Works for literals?	Works for columns?
cast (‘string’ as type)	Yes	Yes
type ‘string’	Yes	No
‘string’::type	Yes	Yes
type(‘string’) – limited options	Yes	Yes

Examples of casting via cast (‘string’ as type)


select tab.data, pg_typeof(tab.data) from (select cast(10 as varchar) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast(10 as text) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10' || '10' as int) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as int) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as text) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as date) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12' as date) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:00:00' as date) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('03-12-2017' as date) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04' as time) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 AM' as time) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 PM' as time) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 AM' as time with time zone) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:01:04 PM' as timestamp) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:01:04 PM' as timestamp with time zone) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('1.2, 123.1' as point) as data) tab;

select tab.data, pg_typeof(tab.data) from (select cast('point(1.2, 123.1), 10' as circle) as data) tab;

create table t_data (name text, age integer, dob timestamp);
insert into t_data values ('John', 10, now()-interval '40 years');
select * from t_data;

select t.name, pg_typeof(t.name), t.age, pg_typeof(t.age), t.dob, pg_typeof(t.dob) from t_data t;

select
tab.name, pg_typeof(tab.name), tab.age, pg_typeof(tab.age)
, tab.dob_date, pg_typeof(tab.dob_date), tab.dob_time, pg_typeof(tab.dob_time)
from
(
select name, cast(age as int), cast(dob as date) dob_date, cast(dob as time) dob_time from t_data
) tab
;

Put the above statements in a file named casting.sql, run on ‘psql’ prompt as below and observe the output.

psql -p 5433 -a -f casting.sql
or
psql -p 5433 -e -c "\i casting.sql"
or
psql
\set ECHO all \i casting.sql

postgres@MKM-HP-ENVY:~$ psql -p 5433 -a -f casting.sql
select tab.data, pg_typeof(tab.data) from (select cast(10 as varchar) as data) tab;
 data |     pg_typeof
------+-------------------
 10   | character varying
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast(10 as text) as data) tab;
 data | pg_typeof
------+-----------
 10   | text
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10' || '10' as int) as data) tab;
 data | pg_typeof
------+-----------
 1010 | integer
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as int) as data) tab;
 data | pg_typeof
------+-----------
      | integer
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as text) as data) tab;
 data | pg_typeof
------+-----------
      | text
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10' || null as date) as data) tab;
 data | pg_typeof
------+-----------
      | date
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12' as date) as data) tab;
    data    | pg_typeof
------------+-----------
 12.03.2017 | date
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:00:00' as date) as data) tab;
    data    | pg_typeof
------------+-----------
 12.03.2017 | date
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('03-12-2017' as date) as data) tab;
    data    | pg_typeof
------------+-----------
 03.12.2017 | date
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04' as time) as data) tab;
   data   |       pg_typeof
----------+------------------------
 10:01:04 | time without time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 AM' as time) as data) tab;
   data   |       pg_typeof
----------+------------------------
 10:01:04 | time without time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 PM' as time) as data) tab;
   data   |       pg_typeof
----------+------------------------
 22:01:04 | time without time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('10:01:04 AM' as time with time zone) as data) tab;
      data      |      pg_typeof
----------------+---------------------
 10:01:04+05:30 | time with time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:01:04 PM' as timestamp) as data) tab;
        data         |          pg_typeof
---------------------+-----------------------------
 12.03.2017 22:01:04 | timestamp without time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('2017-03-12 10:01:04 PM' as timestamp with time zone) as data) tab;
          data           |        pg_typeof
-------------------------+--------------------------
 12.03.2017 22:01:04 IST | timestamp with time zone
(1 row)

select tab.data, pg_typeof(tab.data) from (select cast('1.2, 123.1' as point) as data) tab;
    data     | pg_typeof
-------------+-----------
 (1.2,123.1) | point
(1 row)

create table IF NOT EXISTS t_data (name text, age integer, dob timestamp);
psql:casting.sql:34: NOTICE:  relation "t_data" already exists, skipping
CREATE TABLE
insert into t_data values ('John', 10, now()-interval '40 years');
INSERT 0 1
select * from t_data;
 name | age |            dob
------+-----+----------------------------
 John |  10 | 17.07.1981 10:47:46.203456
(1 row)

select t.name, pg_typeof(t.name), t.age, pg_typeof(t.age), t.dob, pg_typeof(t.dob) from t_data t;
 name | pg_typeof | age | pg_typeof |            dob             |          pg_typeof
------+-----------+-----+-----------+----------------------------+-----------------------------
 John | text      |  10 | integer   | 17.07.1981 10:47:46.203456 | timestamp without time zone
(1 row)

select
tab.name, pg_typeof(tab.name), tab.age, pg_typeof(tab.age)
, tab.dob_date, pg_typeof(tab.dob_date), tab.dob_time, pg_typeof(tab.dob_time)
from
(
        select name, cast(age as int), cast(dob as date) dob_date, cast(dob as time) dob_time from t_data
) tab
;
 name | pg_typeof | age | pg_typeof |  dob_date  | pg_typeof |    dob_time     |       pg_typeof
------+-----------+-----+-----------+------------+-----------+-----------------+------------------------
 John | text      |  10 | integer   | 17.07.1981 | date      | 10:47:46.203456 | time without time zone
(1 row)

postgres@MKM-HP-ENVY:~$

Examples of casting via type ‘string’

select text 'hello', int '10', date '2017-03-04', time '10:00:00 AM';

select float '10.2', numeric '100.0003', timestamp '2017-03-04 10:00:00 AM';

--this will not work on variables or table column
select text name from t_data;

Examples of casting via ‘string’::type

select 'hello'::text, '10'::int, '2017-03-04'::date, '10:00:00 AM'::time with time zone;

select '10.2'::float, '100.0003'::numeric, '2017-03-04 10:00:00 AM'::timestamp;

select name::text, age::text, dob::date, dob::time from t_data;

Examples of casting via type(‘string’)

select text('hello'), int2('10'), int4('10'), date('2017-03-04');

--this does not work
select varchar('hello');

--this does not work
select time('10:00:00 AM');

--this does not work
select float('10.2');

--this does not work
select timestamp('2017-03-04 10:00:00 AM');

Data Types in PostgreSQL

Date Time Operations

◀ Previous Post

Next Post ▶

Leave a Comment Cancel reply