PostgreSQL CAST: Convert a Value of One Data Type to Another

Summary: in this tutorial, you will learn how to use PostgreSQL CAST() function and operator to convert a value of one type to another.

Introduction to PostgreSQL CAST() function and cast operator (::)

There are many cases in which you want to convert a value of one type into another. PostgreSQL offers the CAST() function and cast operator (::) to do this.

PostgreSQL CAST() function

Here’s the basic syntax of the type CAST() function:

CAST(<code>value</code> AS target_type );Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, provide a value that you want to convert. It can be a constant, a table column, or an expression.
Then, specify the target data type to which you want to convert the value.

The CAST() returns a value after it has been cast to the specified target data type. If the CAST() function cannot cast the value to a target type, it’ll raise an error. The error message will depend on the nature of the conversion failure.

PostgreSQL cast operator (::)

Besides the type CAST() function, you can use the following cast operator (::) to convert a value of one type into another:

value::target_typeCode language: SQL (Structured Query Language) (sql)

In this syntax:

value is a value that you want to convert.
target_type specifies the target type that you want to cast the value to.

The cast operator :: returns a value after casting the value to the target_type or raise an error if the cast fails.

Notice that the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard

PostgreSQL CAST() function and cast operator (::) examples

Let’s take some examples of using the CAST operator to convert a value of one type to another.

1) Cast a string to an integer example

The following statement uses the CAST() operator to convert a string to an integer:

SELECT 
  CAST ('100' AS INTEGER);Code language: SQL (Structured Query Language) (sql)

Output:

 int4
------
  100
(1 row)

If the expression cannot be converted to the target type, PostgreSQL will raise an error. For example:

SELECT 
  CAST ('10C' AS INTEGER);Code language: SQL (Structured Query Language) (sql)

[Err] ERROR:  invalid input syntax for integer: "10C"
LINE 2:  CAST ('10C' AS INTEGER);Code language: PHP (php)

2) Cast a string to a date example

This example uses the CAST() function to convert a string to a date:

SELECT
   CAST ('2015-01-01' AS DATE),
   CAST ('01-OCT-2015' AS DATE);Code language: SQL (Structured Query Language) (sql)

Output:

    date    |    date
------------+------------
 2015-01-01 | 2015-10-01
(1 row)

In this example, we converted 2015-01-01 literal string into January 1st 2015 and 01-OCT-2015 to October 1st 2015.

3) Cast a string to a double example

The following example uses the CAST() function to convert a string '10.2' into a double:

SELECT
	CAST ('10.2' AS DOUBLE);Code language: SQL (Structured Query Language) (sql)

Whoops, we got the following error message:

[Err] ERROR:  type "double" does not exist
LINE 2:  CAST ('10.2' AS DOUBLE)Code language: PHP (php)

To fix this, you need to use DOUBLE PRECISION instead of DOUBLE as follows:

SELECT
   CAST ('10.2' AS DOUBLE PRECISION);Code language: SQL (Structured Query Language) (sql)

Output:

 float8
--------
   10.2
(1 row)Code language: CSS (css)

4) Cast a string to a boolean example

This example uses the CAST() to convert the string ‘true’, ‘T’ to true and ‘false’, ‘F’ to false:

SELECT 
   CAST('true' AS BOOLEAN),
   CAST('false' as BOOLEAN),
   CAST('T' as BOOLEAN),
   CAST('F' as BOOLEAN);Code language: SQL (Structured Query Language) (sql)

Output:

 bool | bool | bool | bool
------+------+------+------
 t    | f    | t    | f
(1 row)

5) Cast a string to a timestamp example

This example uses the cast operator (::) to convert a string to a timestamp:

SELECT '2019-06-15 14:30:20'::timestamp;Code language: SQL (Structured Query Language) (sql)

Output:

      timestamp
---------------------
 2019-06-15 14:30:20
(1 row)Code language: CSS (css)

6) Cast a string to an interval example

This example uses the cast operator to convert a string to an interval:

SELECT 
  '15 minute' :: interval, 
  '2 hour' :: interval, 
  '1 day' :: interval, 
  '2 week' :: interval, 
  '3 month' :: interval;Code language: SQL (Structured Query Language) (sql)

Output:

 interval | interval | interval | interval | interval
----------+----------+----------+----------+----------
 00:15:00 | 02:00:00 | 1 day    | 14 days  | 3 mons
(1 row)

7) Cast a timestamp to a date example

The following example uses the CAST() to convert a timestamp to a date:

SELECT CAST('2024-02-01 12:34:56' AS DATE);Code language: SQL (Structured Query Language) (sql)

Output:

    date
------------
 2024-02-01
(1 row)

8) Cast an interval to text

The following example uses CAST() function to convert an interval to text:

SELECT CAST('30 days' AS TEXT);Code language: SQL (Structured Query Language) (sql)

Output:

  text
---------
 30 days
(1 row)

10) Cast a JSON to a JSONB

The following example uses the CAST() function to convert JSON to JSONB:

SELECT CAST('{"name": "John"}' AS JSONB);Code language: SQL (Structured Query Language) (sql)

Output:

      jsonb
------------------
 {"name": "John"}
(1 row)Code language: JavaScript (javascript)

11) Cast a double precision to an integer

The following example uses CAST() function to convert double precision to integer:

SELECT CAST(9.99 AS INTEGER);Code language: SQL (Structured Query Language) (sql)

Output:

 int4
------
   10
(1 row)

12) Cast an array to a text

The following example uses CAST() function to convert an array to text:

SELECT CAST(ARRAY[1, 2, 3] AS TEXT);Code language: PHP (php)

Output:

  array
---------
 {1,2,3}
(1 row)Code language: PHP (php)

13) Cast text to an array

The following example shows how to use the cast operator (::) to convert text to an array:

SELECT '{1,2,3}'::INTEGER[] AS result_array;Code language: PHP (php)

Output:

 result_array
--------------
 {1,2,3}
(1 row)

14) Using CAST with table data example

First, create a ratings table that consists of two columns: id and rating:

CREATE TABLE ratings (
  id SERIAL PRIMARY KEY, 
  rating VARCHAR (1) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some sample data into the ratings table.

INSERT INTO ratings (rating) 
VALUES 
  ('A'), 
  ('B'), 
  ('C');Code language: SQL (Structured Query Language) (sql)

Because the requirements change, we have to use the same ratings table to store ratings as numbers 1, 2, and 3 instead of A, B, and C:

INSERT INTO ratings (rating) 
VALUES 
  (1), 
  (2), 
  (3);Code language: SQL (Structured Query Language) (sql)

Consequentially, the ratings table stores both alphabets & numbers.

SELECT * FROM ratings;Code language: SQL (Structured Query Language) (sql)

Output:

 id | rating
----+--------
  1 | A
  2 | B
  3 | C
  4 | 1
  5 | 2
  6 | 3
(6 rows)

Now, we have to convert all values in the rating column into integers, all other A, B, C ratings will be displayed as zero.

To achieve this, you can use the CASE expression with the type CAST as shown in the following query:

SELECT 
  id, 
  CASE WHEN rating~E'^\\d+$' THEN CAST (rating AS INTEGER) ELSE 0 END as rating 
FROM 
  ratings;Code language: SQL (Structured Query Language) (sql)

Output:

 id | rating
----+--------
  1 |      0
  2 |      0
  3 |      0
  4 |      1
  5 |      2
  6 |      3
(6 rows)

In this example:

rating ~ E'^\\d+$': This expression matches the values in the rating column with a regular expression E'^\\d+$'. The pattern checks if a value contains only digits (\d+) from the beginning (^) to the end ($). The letter E before the string indicates is an escape string.
If the value contains only digits, the CAST() function converts it to an integer. Otherwise, it returns zero.

In this tutorial, you have learned how to use PostgreSQL CAST to convert a value of one type to another.

Was this tutorial helpful ?