CAST Operator: Conversion between two data types

PostgreSQL supports the CAST operator to convert a value from one datatype to another datatype.

Syntax:

CAST(constant|column|expression AS target_type)

In the above syntax, an expression can be a constant, table column, or expression which you want to convert and target_type is the target data type in which you want to get the result.

The CAST operator can be used with SELECT clause, WHERE clause, FROM clause, and INSERT, UPDATE, and DELETE statements.

The following example shows the simple cast operation that converts constant values to the specified target datatype.

Example: Conversion of Contant Values

SELECT 
	CAST('100' AS INTEGER),
	CAST(200 AS TEXT),
   	CAST ('2011-01-15' AS DATE),
	CAST ('15 minutes' AS INTERVAL),
	CAST('2022-06-15 15:10:00' AS TIMESTAMP);

In the above example, it converts text '100' to INTEGER, number 200 to TEXT, string '2011-01-15' to DATE, string '15 minutes' to INTERVAL, and string '2022-06-15 15:10:00' to TIMESTAMP. The above casting will display the following result in pgAdmin:

If constant, expression or column cannot be converted to target datatype, PostgreSQL will raise an error like bellow.

The CAST operator can be used to cast a string to a boolean datatype, as shown below. Please note Boolean data type considers true, T, 1 as true and false, F, 0 as false.

Example: Convert to Boolean

SELECT 
   CAST('true' AS BOOLEAN),
   CAST('false' as BOOLEAN),
   CAST('T' as BOOLEAN),
   CAST('F' as BOOLEAN),   
   CAST('1' as BOOLEAN),
   CAST('0' as BOOLEAN);

The CAST operator can be applied to an expression that evaluates to some value or table column also.

PostgreSQL does implicit type conversion, for example, if you try to perform a mathematical operation on one numeric value and another text datatype with a numeric value.

Above 100 is numeric and '2' is text, when we try to perform an addition operation between them, PostgreSQL converts text to an integer, performs addition to 100, and gives the result as integer.

PostgreSQL supports type cast operator :: to convert the value of one data type to another.

Note: The cast syntax using the cast operator :: is PostgreSQL-specific and does not belong to SQL standards.

CAST Column Values

Let's use the following Employee table to see how to cast column values.

The above Salary column is Integer, we can CAST it to NUMERIC with 10 digits and precision as 2.

Example: Cast Column Values

SELECT first_name, last_name, CAST(salary AS NUMERIC(10,2))  FROM Employee;