SQL : CAST function

In PostgreSQL, the CAST function is used to explicitly convert a value from one data type to another. It is an alternative to using the shorthand :: operator for type conversion.

Syntax:

CAST (value AS target_type)
  • value: The value or column you want to convert.
  • target_type: The target data type you want to convert the value to.

Examples:

1. Converting a String to an Integer

If you have a string representation of a number and you want to convert it to an integer, you can use CAST.

SELECT CAST('123' AS INTEGER) AS converted_value;

Output:

 converted_value
-----------------
             123

2. Converting a Number to a String

You can convert a number to a string using CAST.

SELECT CAST(456 AS TEXT) AS converted_value;

Output:

 converted_value
-----------------
 456

3. Converting a String to a Numeric

If you have a string with a floating-point number, you can convert it to a numeric type.

SELECT CAST('123.45' AS NUMERIC) AS converted_value;

Output:

 converted_value
-----------------
           123.45

4. Converting a Date to a String

You can convert a DATE type to a string for formatting purposes.

SELECT CAST(current_date AS TEXT) AS converted_value;

Output:

 converted_value
-----------------
 2024-09-11

5. Using CAST in a Query with a Column

If you have a table column of one data type (e.g., price as numeric) and want to convert it to another type (e.g., text), you can use CAST.

Assume you have a table products with a price column (numeric):

SELECT name, CAST(price AS TEXT) AS price_text
FROM products;

Difference between CAST and ::

  • CAST is ANSI SQL standard and works in all SQL-compliant databases.
  • :: is shorthand for casting and is specific to PostgreSQL.

Example of :: shorthand:

SELECT '123'::INTEGER;

Summary:

  • CAST converts a value from one data type to another.
  • It can be used for conversions between text, numeric, date, and other data types.
  • CAST provides an explicit and readable way to perform type conversions in SQL.

links

social