TRY_CAST

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Converting types. TRY_CAST(x AS type) converts a value like CAST, but returns NULL for a value that doesn't convert or doesn't fit the type, instead of stopping the query. Use it on text you don't control.

Syntax

TRY_CAST(x AS type)

Returns: a value of type, or NULL. The result has the target type even when it's NULL: typeof(TRY_CAST('x' AS INTEGER)) is 'INTEGER'.

The keyword can be written in any case: try_cast works too.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_conditional_and_cast_try_cast. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of six readings loaded from a file as text. Some values are numbers and valid dates, some aren't; the last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_try_cast"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, raw VARCHAR, day_text VARCHAR, seen DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '42', '2026-10-16', DATE '2026-10-16'), "
    "(2, ' 12 ', '2026-02-30', DATE '2026-10-17'), "
    "(3, 'ten', '16/10/2026', DATE '2026-10-18'), "
    "(4, '1e3', '2024-02-29', DATE '2026-10-19'), "
    "(5, '', 'today', DATE '2026-10-20'), "
    "(6, NULL, NULL, NULL)")

[]
[]
[(6,)]

What it does

TRY_CAST(x AS type) applies the same conversion rules as CAST: the same text formats, rounding and ranges. Where CAST would fail on a value, TRY_CAST returns NULL for that value and the query carries on:

• Text that doesn't parse as the type: 'ten' as an INTEGER, '16/10/2026' as a DATE, 'abc' as JSON.
• Values out of range: 300 as a TINYINT, -1 as a UINTEGER, '12345.678' as a DECIMAL(5,2), NaN or infinity as an integer.
• Dates that don't exist: '2026-02-30'.
• Inside a list, element by element: TRY_CAST('[1, x]' AS INTEGER[]) is [1, NULL]; text that isn't a list at all, such as '1,2', gives NULL.
• Pairs of types with no conversion: a DATE to an INTEGER gives NULL for every row, where CAST fails with Unimplemented type for cast. See Limitations and errors.

Values that convert give the same result as CAST: TRY_CAST(' 12 ' AS INTEGER) is 12, and TRY_CAST(-1 AS BOOLEAN) is true.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT TRY_CAST(NULL AS INTEGER), TRY_CAST('ten' AS INTEGER), typeof(TRY_CAST('ten' AS INTEGER)), "
    "TRY_CAST('300' AS TINYINT), TRY_CAST(300 AS TINYINT), TRY_CAST(-1 AS UINTEGER)")
run("SELECT TRY_CAST('2026-02-30' AS DATE), TRY_CAST('12345.678' AS DECIMAL(5,2)), "
    "TRY_CAST('nan'::DOUBLE AS INTEGER), TRY_CAST('abc' AS JSON)")
run("SELECT TRY_CAST('[1, x]' AS INTEGER[]), TRY_CAST([1, 300] AS TINYINT[]), TRY_CAST('1,2' AS INTEGER[])")
run("SELECT TRY_CAST(DATE '2026-10-16' AS INTEGER), TRY_CAST(-1 AS BOOLEAN), try_cast('7' AS INTEGER)")
run("SELECT TRY_CAST('x' AS NOSUCHTYPE)")
run("SELECT TRY_CAST({'a': 1} AS STRUCT(b INTEGER))")

[(None, None, 'INTEGER', None, None, None)]
[(None, None, None, None)]
[([1, None], [1, None], None)]
[(None, True, 7)]
InternalError: Catalog Error: Type with name NOSUCHTYPE does not exist!
InternalError: Binder Error: STRUCT to STRUCT cast must have at least one matching member

Examples

Convert the text readings to numbers. Values that aren't numbers become NULL, and the query returns every row; CAST on the same column fails on 'ten':

run(f"SELECT id, raw, TRY_CAST(raw AS INTEGER), TRY_CAST(raw AS DOUBLE) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, CAST(raw AS INTEGER) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '42', 42, 42.0), (2, ' 12 ', 12, 12.0), (3, 'ten', None, None), (4, '1e3', 1000, 1000.0), (5, '', None, None), (6, None, None, None)]
OSError: Could not convert string 'ten' to INT32

Convert text dates. Only text in year-month-day order that names a real date converts:

run(f"SELECT id, day_text, TRY_CAST(day_text AS DATE) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '2026-10-16', datetime.date(2026, 10, 16)), (2, '2026-02-30', None), (3, '16/10/2026', None), (4, '2024-02-29', datetime.date(2024, 2, 29)), (5, 'today', None), (6, None, None)]

Find the rows whose value doesn't convert. TRY_CAST also returns NULL for a NULL input, so test the input too:

run(f"SELECT id, raw FROM {TABLE} WHERE raw IS NOT NULL AND TRY_CAST(raw AS INTEGER) IS NULL ORDER BY id LIMIT 10")

[(3, 'ten'), (5, '')]

Use a default for the values that don't convert, with coalesce:

run(f"SELECT id, coalesce(TRY_CAST(raw AS INTEGER), 0) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 42), (2, 12), (3, 0), (4, 1000), (5, 0), (6, 0)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a conversion of a column, so TRY_CAST() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine converts it for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine converts each value and keeps the rows that match. No rows are skipped in storage.

The plan of a filter on a converted value shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE TRY_CAST(raw AS INTEGER) > 5")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (TRY_CAST(raw AS INTEGER) │
│            > 5)           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            raw            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Convert once, when you load the data. Store the converted value in a column of the right type, and keep the original text in another column if you need it. A comparison of the typed column with a constant is pushed down to VAST DataBase; TRY_CAST(raw AS INTEGER) > 5 converts every row in the Query Engine.

Limitations and errors

• A pair of types with no conversion gives NULL for every row, silently. TRY_CAST(seen AS INTEGER) on a DATE column returns only NULLs, where CAST fails with Unimplemented type for cast. If a TRY_CAST column is all NULL, run the same conversion with CAST on one value to see why.
• A NULL result doesn't say why. It's NULL for a NULL input and for a value that doesn't convert alike; test x IS NOT NULL as well, as in the examples.
• Errors about the query itself are still raised. An unknown type name fails with Catalog Error: Type with name ... does not exist!, and a STRUCT cast with no field in common fails with STRUCT to STRUCT cast must have at least one matching member.
• Other names aren't available. try(expression) fails with Scalar Function with name try does not exist!, and safe_cast(x AS type) with a syntax error. Use TRY_CAST.

run(f"SELECT id, seen, TRY_CAST(seen AS INTEGER) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT CAST(seen AS INTEGER) FROM {TABLE} LIMIT 1")
run("SELECT try(CAST('x' AS INTEGER))")

[(1, datetime.date(2026, 10, 16), None), (2, datetime.date(2026, 10, 17), None), (3, datetime.date(2026, 10, 18), None), (4, datetime.date(2026, 10, 19), None), (5, datetime.date(2026, 10, 20), None), (6, None, None)]
OSError: Unimplemented type for cast (DATE -> INTEGER)
InternalError: Catalog Error: Scalar Function with name try does not exist!

Similar functions

run("SELECT TRY_CAST('42' AS INTEGER), TRY_CAST('ten' AS INTEGER), TRY_CAST(NULL AS INTEGER), "
    "CAST('42' AS INTEGER), CAST(NULL AS INTEGER), coalesce(TRY_CAST('42' AS INTEGER), 0), "
    "coalesce(TRY_CAST('ten' AS INTEGER), 0), coalesce(TRY_CAST(NULL AS INTEGER), 0)")
run("SELECT CAST('ten' AS INTEGER)")

[(42, None, None, 42, None, 42, 0, 0)]
OSError: Could not convert string 'ten' to INT32

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Conditional and conversion functions: all the conversion functions.
• CAST and ::: the conversion rules.
• Data types in SQL: type names.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.