PostgreSQL – CAST Function

The CAST function is used to explicitly convert a value from one data type to another. It allows you to change the data type of a value, expression, or column to a different data type. This can be useful to perform operations that require compatible data types or when you need to ensure that the data is presented in a specific format.

The basic syntax of the CAST function in PostgreSQL is as follows:

CAST(expression AS target_data_type)

where expression is the value or expression that you want to cast.

and target_data_type is the data type to which you want to cast the expression.

Here are a few examples of how you can use the CAST function in PostgreSQL:

1. Casting to a Different Numeric Type:

SELECT CAST(5.75 AS INTEGER); -- Casts the decimal value to an integer

2. Casting to a Different Text Type:

SELECT CAST(12345 AS VARCHAR); -- Casts the integer to a string

3. Casting Date and Time Types:

SELECT CAST('2023-08-21' AS DATE); -- Casts a string to a DATE type 

SELECT CAST('15:30:00' AS TIME); -- Casts a string to a TIME type

4. Casting Text to Numeric:

SELECT CAST('42' AS INTEGER); -- Casts a string to an integer 

SELECT CAST('3.14' AS FLOAT); -- Casts a string to a floating-point number

5. Casting Between Boolean and Other Types:

SELECT CAST(1 AS BOOLEAN); -- Casts an integer to BOOLEAN (true) 

SELECT CAST('false' AS BOOLEAN); -- Casts a string to BOOLEAN (false)

Not all casts are valid, and some conversions might result in errors if the data types are not compatible. PostgreSQL provides various built-in data types, and casting is essential for transforming values between these types as needed in various queries and operations.