Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-cast/ below:

PostgreSQL - CAST - GeeksforGeeks

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.

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.

Let us take a look at some of the examples of CAST Operator in PostgreSQL to better understand the concept.

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:

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.

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.

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)

SELECT '100'::INTEGER;

Output:

Explanation:

This cast operator achieves the same result as CAST ('100' AS INTEGER) but with a simplified syntax.

• 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4