PostgreSQL CAST (original) (raw)
Last Updated : 15 Jul, 2025
The **PostgreSQL CAST function provides an efficient way to **convert data types in PostgreSQL, which is important when ensuring data is in the correct format for storage, calculations, or comparisons.
In PostgreSQL, we can use **CAST to transform data between various data types, such as **converting strings to integers, dates, or booleans. This article will guide you through the **PostgreSQL CAST syntax, examples of its usage, and important considerations.
What is CAST in PostgreSQL?
In PostgreSQL, the **CAST function is a **flexible **operator that allows us to change the data type of an expression to a target data type. This function is essential in situations where the data type of a value must match the **requirements of an operation or a query.
**Syntax
CAST ( expression AS target_type );
**Key Terms
- **Expression: The value to be converted. This can be a constant, a table column, or any expression that evaluates to a value.
- **Target_Type: The data type to which the expression is to be converted.
PostgreSQL CAST Operator Examples
Let us take a look at some of the examples of **CAST Operator in **PostgreSQL to better understand the concept.
**Example 1: Converting a String to an Integer
The following statement converts a string constant to an integer:
**Query:
SELECT
CAST ('100' AS INTEGER);
**Output
If the expression cannot be converted to the target type, PostgreSQL will raise an error. See the following:
SELECT
CAST ('10C' AS INTEGER);
**Error Output
This will result to the below-depicted error:
**Example 2: Converting a String to a Date
The following example demonstrates how to **convert a string to a date format in PostgreSQL:
**Query:
SELECT
CAST ('2020-01-01' AS DATE),
CAST ('01-OCT-2020' AS DATE);
**Output
**Explanation:
The result will be the date values '2020-01-01' and '2020-10-01', respectively.
**Example 3: Converting Strings to Boolean Values
In PostgreSQL, we can also use **CAST to convert string values like ' true', ' T', ' false', and ' F' to boolean values. Here’s an example:
**Query:
SELECT
CAST('true' AS BOOLEAN),
CAST('false' as BOOLEAN),
CAST('T' as BOOLEAN),
CAST('F' as BOOLEAN);
**Output
**Explanation:
The result will be 'true', 'false', 'true', and 'false', respectively.
**Using CAST vs Cast Operator ( :: ) in PostgreSQL
In **PostgreSQL, we have two ways to perform type casting:
- **CAST function: The standard SQL syntax (
CAST(expression AS target_type)) - **Cast operator (
::): PostgreSQL's shorthand notation (expression::target_type)
Example of Cast Operator Usage
SELECT '100'::INTEGER;
**Output:
| INTEGER |
|---|
| 100 |
**Explanation:
This cast operator achieves the same result as CAST ('100' AS INTEGER) but with a simplified syntax.
Important Points About PostgreSQL CAST Operator
- **PostgreSQL allows you to **cast JSON or JSONB data types to text or other compatible types.
- **PostgreSQL supports user-defined domain types. You can cast values to these domain types to enforce additional constraints.
- **CAST operations can result in errors if the conversion is not possible.
- We can use **CAST in conjunction with **COLLATE to change the collation of text data, which can affect sorting and comparison.
**Conclusion
The **PostgreSQL CAST function is an essential tool for **data type conversion, providing a reliable way to transform data between formats like **string to integer, date, or boolean. Whether you use the **CAST function or the **cast operator ( :: ), these conversions improve **data accuracy and **streamline PostgreSQL queries. Keep in mind that **incompatible conversions will raise errors, so always ensure **data compatibility for seamless operations.