Use TRY_CAST to convert data types and return NULL for conversion errors - Hologres

TRY_CAST converts a TEXT value to a specified data type. Unlike CAST, it never throws an error on conversion failure — it returns NULL instead. Use TRY_CAST when you want to handle bad input gracefully rather than abort the query.

Note

Hologres also supports other type conversion functions. For more information, see Date and time functions.

Limitations

TRY_CAST requires Hologres V2.2 or later.

Usage notes

The input expression (source_string_expr) must be of type TEXT.
target_data_type must be one of the following: INTEGER, SMALLINT, BIGINT, REAL, DOUBLE PRECISION, BOOLEAN, DECIMAL, JSONB, DATE, TIMESTAMP, or TIMESTAMPTZ.
DATE, TIMESTAMP, and TIMESTAMPTZ require Hologres V3.0 or later.
If the value cannot be converted to the target type, NULL is returned.

Syntax

TRY_CAST( <source_string_expr> AS <target_data_type> )

Parameters

Parameter	Data type	Description
`source_string_expr`	TEXT	The column or expression to convert.
`target_data_type`	See Usage notes	The target data type. Returns NULL if the value cannot be converted.

Examples

Convert TEXT to INT

Create a table and insert test data, then use TRY_CAST to convert the value column from TEXT to INT.

-- Create a table and insert test data
CREATE TABLE IF NOT EXISTS try_cast_test (
  key   int,
  value text
);

INSERT INTO try_cast_test VALUES (1, '1'), (2, 'abc');

-- Convert the value column from TEXT to INT
SELECT key, value, TRY_CAST(value AS INT) AS value_int
FROM try_cast_test;

Result:

 key | value | value_int
-----+-------+-----------
   1 | 1     |         1
   2 | abc   | \N
(2 rows)

The value 'abc' cannot be converted to INT, so TRY_CAST returns NULL for that row.

Use TRY_CAST with CASE WHEN to handle conversion results

Combine TRY_CAST with CASE WHEN to branch on whether a conversion succeeds. This is the most common pattern for acting on TRY_CAST results in production queries.

SELECT
  value,
  CASE
    WHEN TRY_CAST(value AS INT) IS NULL THEN 'Not a valid integer'
    ELSE 'Valid integer'
  END AS conversion_status
FROM try_cast_test;

Result:

 value | conversion_status
-------+---------------------
 1     | Valid integer
 abc   | Not a valid integer
(2 rows)