How to convert one data type to another in PostgreSQL

A type cast use to convert from one data type to another.

Basically there two type of casting

  1. Explicit Type Cast – Explicitly convert one data type to another
  2. Implicit Type Cast – PostgreSQL internally convert one data type to another if it is compatible

Explicit Type Cast

PostgreSQL accepts two equivalent explicit cast syntax

  1. CAST (expression as type)
  2. expression :: type

CAST syntax is ANSI standard and second one(::) is historical PostgreSQL usage

When a cast is applied to a value expression of a known type, it represents a run-time type conversion

Some examples

  • Example 1 – String to Integer
  • Example 2 – Integer to Varchar
  • Example 3 – String to Float
  • Example 4 – String to Double
  • Example 5 – String to Date
  • Example 6 – String to Boolean
  • Example 7 – Boolean to String
  • Example 8 – String to Timestamp
  • Example 9 – String to XML
  • Example 10 – Integer to Bit

Example 1 – String to Integer :-

postgres=# select cast('2' as int)  + cast('3' as int) as sum;
sum
----
 5
(1 row)

Example 2 – Integer to Varchar :-

postgres=# select cast('100' as varchar);                                                                                     

varchar 
---------
 100
(1 row)

Example 3 – String to Float :-

postgres=# select cast('2.5' as float) + cast('3.2' as float) as sum;
 sum 
-----
 5.7
(1 row)

Example 4 – String to Double :-

postgres=# select cast('2.57856545' as double precision) + cast('3.2' as float) as sum;
    sum     
------------
 5.77856545
(1 row)

Example 5 – String to Date :-

postgres=# select cast('2022-08-29' as date) + 1 as tomorrow  ;
  tomorrow  
------------
 2022-08-30
(1 row)

Example 6 – String to Boolean :-

postgres=# select 
cast('True' as boolean),
cast('FALSE' as boolean),
cast('1' as boolean),
cast('0' as boolean);
 bool | bool | bool | bool 
------+------+------+------
 t    | f    | t    | f

Example 7 – Boolean to String :-

postgres=# select 
cast(cast ('t' as boolean) as text);
 text 
------
 true
(1 row)

postgres=# select 
cast(cast ('f' as boolean) as text);
 text  
-------
 false
(1 row)

Example 8 – String to Timestamp :-

postgres=# select cast('2022-08-30 04:08:03 pm' as timestamp);
      timestamp      
---------------------
 2022-08-30 16:08:03
(1 row)

Example 9 – String to XML :-

postgres=# select 
cast('<xml/>' as xml );
  xml   
--------
 <xml/>
(1 row)

Example 10 – Integer to Bit :-

postgres=# select cast(1 as bit);
 bit 
-----
 1
(1 row)

postgres=# select cast(0 as bit);
 bit 
-----
 0
(1 row)

Implicit Type Cast

PostgreSQL automatically converts one data type to another if compatible, see below example

postgres=# \d emp;                                  Table "public.emp"
 Column  |         Type          | Collation | Nullable |              Default               
---------+-----------------------+---------
 empno   | integer       not null | 
 empname | character varying(20)  |           
 salary  | integer                |           
 dob     | date                   |           
 deptno  | integer                |           
 bonus   | integer                |           

postgres=# insert into emp values(19,'Tom','8500',cast('1975-01-29' as date),3,975);
INSERT 0 1

postgres=# select * from emp where empno = 19;
empno|empname|salary|dob| deptno | bonus 
-------+---------+--------+------------+--
 19 | Tom |8500 | 1975-01-29 |3 | 975
(1 row)

In Emp table salary is an integer data type, we have specified String value in the insert query and PostgreSQL implicitly converted string into integer and successfully inserted the row

Limitation

  1. The cast will succeed only if a suitable type conversion operation has been defined. Not all data type can be converted to other data type
  2. Implicit casting is an expensive operation

Conclusion

We have learned about type cast (both Explicit and Implicit) in PostgreSQL. Casting is very useful when we do data load (Part of ETL), we may get inconsistent data type values in the in coming feed/flat files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s