CAST | PostgreSQL Tutorial

PostgreSQL offers two type conversion syntaxes: the SQL-standard CAST(value AS target_type) function and the PostgreSQL-specific cast operator value::target_type. Both produce the same result. Use CAST() for portability across databases; use :: for brevity in PostgreSQL-only code.

CAST() function and :: operator syntax

-- SQL standard
CAST(value AS target_type)

-- PostgreSQL shorthand (not in SQL standard)
value::target_type

If the value cannot be converted, PostgreSQL raises an error. For example, casting a non-numeric string to an integer:

SELECT CAST('10C' AS INTEGER);
-- ERROR: invalid input syntax for type integer: "10C"

Common type conversion examples

String to integer:

SELECT CAST('100' AS INTEGER);  -- returns 100

String to date:

SELECT CAST('2015-01-01' AS DATE),
       CAST('01-OCT-2015' AS DATE);
-- 2015-01-01 | 2015-10-01

String to double precision (use DOUBLE PRECISION, not DOUBLE):

SELECT CAST('10.2' AS DOUBLE PRECISION);  -- returns 10.2

String to boolean:

SELECT CAST('true' AS BOOLEAN),
       CAST('T' AS BOOLEAN),
       CAST('false' AS BOOLEAN),
       CAST('F' AS BOOLEAN);
-- t | t | f | f

String to timestamp using :: shorthand:

SELECT '2019-06-15 14:30:20'::TIMESTAMP;
-- 2019-06-15 14:30:20

Timestamp to date (truncates time):

SELECT CAST('2024-02-01 12:34:56' AS DATE);
-- 2024-02-01

Double precision to integer (rounds to nearest):

SELECT CAST(9.99 AS INTEGER);  -- returns 10

Text to array:

SELECT '{1,2,3}'::INTEGER[] AS result_array;
-- {1,2,3}

JSON to JSONB:

SELECT CAST('{"name": "John"}' AS JSONB);
-- {"name": "John"}

CAST with table data and CASE

When a column stores mixed content (letters and digits as strings), combine CASE with CAST to safely convert numeric values while defaulting others to zero:

SELECT
  id,
  CASE
    WHEN rating ~ E'^\d+$' THEN CAST(rating AS INTEGER)
    ELSE 0
  END AS rating_int
FROM ratings;

The regex E'^\d+$' matches strings containing only digits, ensuring the cast only runs on convertible values.

Practical tips

PostgreSQL uses DOUBLE PRECISION, not DOUBLE—using DOUBLE alone raises a type-not-found error.
The :: operator is PostgreSQL-specific. Use CAST() when writing SQL that must also run on other databases.
Casting a floating-point value to integer rounds to the nearest integer using banker's rounding rules.
Implicit casts happen automatically in many contexts (e.g., comparing integer columns with string literals), but explicit casts make the intent clear and can prevent surprising errors.

Frequently Asked Questions

What is the difference between CAST() and :: in PostgreSQL?

Both convert a value to a target type and produce identical results. CAST(value AS type) is the SQL standard form. value::type is PostgreSQL-specific shorthand. Use CAST() for cross-database compatibility and :: for concise PostgreSQL-only code.

How do I convert a string to a date in PostgreSQL?

Use CAST('2024-01-15' AS DATE) or '2024-01-15'::DATE. PostgreSQL accepts many date formats including ISO 8601 (YYYY-MM-DD) and formats like '01-OCT-2024'. The TO_DATE() function gives more control over custom formats.

What happens when a CAST fails?

PostgreSQL raises an error and aborts the statement. To handle potential cast failures gracefully, use a CASE expression to validate the value first (e.g., check with a regex that the string contains only digits before casting to integer).

Why does CAST('10.2' AS DOUBLE) fail in PostgreSQL?

PostgreSQL does not have a type named DOUBLE. The correct type name is DOUBLE PRECISION. Use CAST('10.2' AS DOUBLE PRECISION) or '10.2'::DOUBLE PRECISION.

Can I cast NULL in PostgreSQL?

Yes. CAST(NULL AS INTEGER) returns NULL of type integer. This is useful when constructing queries with UNION where column types must match, or when passing typed nulls to functions.