PostgreSQL CAST Convert From One Data Type Into Another

Summary: in this tutorial, we will show you how to use PostgreSQL CAST operator to convert a value of one type to another.

Introduction to PostgreSQL `CAST` operator

There are many cases that you want to convert a value of one data type into another. PostgreSQL provides you with the CAST operator that allows you to do this.

The following illustrates the syntax of type CAST:

CAST ( expression AS target_type );
Code language: SQL (Structured Query Language) (sql)

In this syntax:

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

PostgreSQL type cast :: operator

Besides the type CAST syntax, you can use the following syntax to convert a value of one type into another:

expression::type
Code language: SQL (Structured Query Language) (sql)

See the following example:

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

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

PostgreSQL `CAST` 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 converts a string constant to an integer:

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

If the expression cannot be converted to the target type, PostgreSQL will raise an error. See the following 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 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)

PostgreSQL CAST - convert a string to a date

First, we converted 2015-01-01 literal string into January 1st 2015. Second, we converted 01-OCT-2015 to October 1st 2015.

3) Cast a string to a double example

In the following example, we try to convert a string '10.2' into a double value:

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)

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)

Here is the output:

5) Convert 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)

6) Convert 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)

Here is the output:

PostgreSQL CAST - cast a string to an interval

7) Using `CAST` with table data example

First, create a ratings table that consists of two columns: id and rating. The data type of the rating column is VARCHAR(1):

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 use the same ratings table to store ratings as number e.g., 1, 2, 3 instead of A, B, and C:

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

So the ratings table stores mixed values including numeric and string.

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

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 do this, you 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)

The CASE checks the rating, if it matches the integer pattern, it converts the rating into an integer, otherwise, it returns 0.

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

Was this tutorial helpful ?

Introduction to PostgreSQL CAST operator