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:
- Casting to a Different Numeric Type:
SELECT CAST(5.75 AS INTEGER); -- Casts the decimal value to an integer
- Casting to a Different Text Type:
SELECT CAST(12345 AS VARCHAR); -- Casts the integer to a string
- 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
- 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
- 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.