CAST and TRY_CAST

The cast function and operator return a value converted to the specified type. The try_cast function does the same, but returns NULL instead of an error when the value cannot be converted.

Signatures

CAST ( <val> AS <type> )

<val>::<type>

The following special syntax is permitted if val is a string literal:

<type> '<val>'

TRY_CAST ( <val> AS <type> )

Return value

cast returns the value with the type specified by the type parameter.

try_cast returns the value with the type specified by the type parameter, or NULL if the conversion fails. Its result is nullable whenever the conversion can fail, even when the value being converted is not.

Details

Valid casts

Cast context defines when casts may occur.

Casts allowed in less strict contexts are also allowed in stricter contexts. That is, implicit casts also occur by assignment, and both implicit casts and casts by assignment can be explicitly invoked.

¹ Arrays and lists are composite types subject to special constraints. See their respective type documentation for details.

² Casting a float to a numeric can yield an imprecise result due to the floating point arithmetic involved in the conversion.

TRY_CAST

TRY_CAST accepts the same explicit casts as CAST, and returns NULL where CAST would return an error: for example, text that does not parse as the target type, a number that does not fit in the target type, or a JSON value of the wrong kind. When the conversion succeeds, TRY_CAST and CAST return the same value.

A CAST that fails on one row makes every query that needs that row’s value fail, for as long as the row is present. In a materialized view or an indexed view, that is every query against the view, whatever it selects. TRY_CAST returns NULL for that row instead.

TRY_CAST only suppresses errors from the conversion itself. Errors raised while computing the value being converted still propagate:

SELECT TRY_CAST(1 / 0 AS text);

ERROR:  division by zero

A parameter whose type is inferred from TRY_CAST is decoded as the target type when the statement is bound, so a bound value that does not convert is a bind error rather than NULL. To get NULL instead, give the parameter an explicit type:

SELECT TRY_CAST($1::text AS int);

Converting a value of a composite type (array, list, or record) converts each element. If any element fails to convert, TRY_CAST returns NULL for the whole value rather than a value with NULL elements.

TRY_CAST accepts every explicit cast that CAST does, with these differences, each of which is reported when the statement is planned:

• TRY_CAST does not support the casts that look up objects in the catalog: casts to or from regclass, regproc, regtype, aclitem, and mz_aclitem, other than those between the oid alias types and the integer types.

• A non-empty ARRAY, LIST, or ROW constructor written directly inside TRY_CAST is typed on its own before the conversion, where CAST would type it from the target. Its elements must therefore already agree on a type:

  -- With a text column s, CAST(ARRAY[s, 2] AS int[]) plans, but this does not:
  -- "ARRAY types text and integer cannot be matched".
  SELECT TRY_CAST(ARRAY[s, 2] AS int[]) FROM t;
  -- Give the elements a common type instead.
  SELECT TRY_CAST(ARRAY[s, 2::text] AS int[]) FROM t;
  

  An empty ARRAY, LIST, or MAP constructor has no elements to convert, so it takes its type from the target as it does under CAST.

• A non-empty MAP constructor written directly inside TRY_CAST is rejected. It would be typed on its own like the other constructors, and there is no cast between two map types to convert it afterward. Convert it with CAST or :: instead.

Examples

SELECT INT '4';

 ?column?
----------
         4

SELECT CAST (CAST (100.21 AS numeric(10, 2)) AS float) AS dec_to_float;

 dec_to_float
--------------
       100.21

SELECT 100.21::numeric(10, 2)::float AS dec_to_float;

 dec_to_float
--------------
       100.21

SELECT TRY_CAST('42' AS int) AS ok, TRY_CAST('forty-two' AS int) AS bad;

 ok | bad
----+-----
 42 |

Routing rows whose values do not convert, instead of failing the whole view:

CREATE VIEW parsed AS
  SELECT raw, TRY_CAST(raw AS timestamp) AS ts
  FROM events;

CREATE VIEW unparseable AS
  SELECT raw FROM parsed WHERE ts IS NULL AND raw IS NOT NULL;

Related topics

• Data Types