PostgreSQL - CAST

The CAST operator in PostgreSQL is used to convert a value from one data type to another. It helps perform operations on compatible data types and improves the readability of SQL queries.

• Perform calculations on converted values.
• Format query results in the required data type.
• Ensure compatibility between different data types.

Syntax

CAST(expression AS data_type)

or

expression::data_type

Where:

• expression: The value or column to be converted.
• data_type: The target data type.

Working

Firstly, create a products table and insert some records.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price VARCHAR(20)
);

INSERT INTO products VALUES
(101, 'Laptop', '75000'),
(102, 'Keyboard', '2500'),
(103, 'Mouse', '1200');

Output:

Example 1: Basic CAST

The following query converts the price column from text to an integer.

Query:

SELECT product_name,
       CAST(price AS INTEGER) AS price
FROM products;

Output:

• The CAST() function converts the price column from VARCHAR to INTEGER.
• The converted values are returned as integers.

Example 2: CAST with Arithmetic Operation

The following query converts the price column into an integer and adds 500 to each product price.

Query:

SELECT product_name,
       CAST(price AS INTEGER) + 500 AS updated_price
FROM products;

Output:

• The CAST() function converts the price column into an integer.
• PostgreSQL performs the arithmetic operation after the conversion.
• The query returns the updated price for each product.

Example 3: Using the :: Type Cast Operator

The following query converts the price column into an integer using PostgreSQL's :: type cast operator.

Query:

SELECT product_name,
       price::INTEGER AS price
FROM products;

Output:

• The :: operator is PostgreSQL's shorthand syntax for type conversion.
• It produces the same result as the CAST() function while using a shorter syntax.