Conversion functions

Converts values to a specified data type.

Syntax

CAST(<expr> AS <type>)
<expr>::<type>
TRY(CAST(<expr> AS <type>))
TRY(<expr>::<type>)

Arguments

Argument	Type	Description
`<expr>`	Any type	Value to convert.
`<type>`	Type name	Target data type, such as `int`, `string`, or `timestamp`.

Returns

Returns the target type. A NULL input remains NULL. A failed conversion raises an error unless wrapped in TRY(...).

CAST and :: perform the same conversion. The :: operator binds more tightly than arithmetic, so use (price_cents * quantity)::string to cast the full line amount. See data type conversion for supported conversions.

TRY CAST

TRY(...) returns NULL when evaluating the cast fails. It does not make invalid syntax, unknown columns, or invalid function signatures valid. Use CATCH(<expr>) to inspect an evaluation error as text; it returns NULL if the expression succeeds.

Examples

Convert producer measurements

Convert a text amount into integer cents, and interpret a nanosecond timestamp supplied by a producer.

SELECT
    CAST('7900' AS int) AS total_cents,
    1780308000000000000::timestamp AS event_time

+-------------+----------------------+
| total_cents | event_time           |
+-------------+----------------------+
| 7900        | 2026-06-01T10:00:00Z |
+-------------+----------------------+

Keep malformed measurements visible

A producer sends duration as text, but one event contains a non-numeric value. Convert valid durations while keeping the bad record visible.

VALUES ('evt-17', '245'), ('evt-42', 'unknown'), ('evt-63', '1800')
SELECT $0 AS event_id, $1 AS raw_duration
SELECT event_id, raw_duration, TRY(raw_duration::int) AS duration_ms

+----------+--------------+-------------+
| event_id | raw_duration | duration_ms |
+----------+--------------+-------------+
| evt-17   | 245          | 245         |
| evt-42   | unknown      | NULL        |
| evt-63   | 1800         | 1800        |
+----------+--------------+-------------+

Check NULL conversion results before aggregation; otherwise an aggregate that skips NULL values could silently omit a malformed measurement.

Diagnose conversion errors

Pair TRY and CATCH to return converted durations alongside any conversion errors.

VALUES ('evt-17', '245'), ('evt-42', 'unknown'), ('evt-64', NULL)
SELECT $0 AS event_id, $1 AS raw_duration
SELECT
    event_id,
    TRY(raw_duration::int) AS duration_ms,
    CATCH(raw_duration::int) AS conversion_error
ORDER BY event_id

+----------+-------------+----------------------------------------------------------------+
| event_id | duration_ms | conversion_error                                               |
+----------+-------------+----------------------------------------------------------------+
| evt-17   | 245         | NULL                                                           |
| evt-42   | NULL        | cannot convert `unknown` to int: invalid digit found in string |
| evt-64   | NULL        | NULL                                                           |
+----------+-------------+----------------------------------------------------------------+

The invalid duration produces an error message. The missing duration produces NULL in both columns because casting NULL succeeds.