can_cast_implicitly

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Converting types. Returns true if a value of the first argument's type converts to the second argument's type without an explicit CAST, for example INTEGER to BIGINT. Only the types of the arguments matter, not their values.

Syntax

can_cast_implicitly(source, target)

Returns: BOOLEAN.

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_can_cast_implicitly. 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 three readings, each with a column of a different type. 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_can_cast_implicitly"'

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, small INTEGER, big BIGINT, ratio DOUBLE, label VARCHAR, day DATE, at TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 7, 7000000000, 0.5, 'north', DATE '2026-10-16', TIMESTAMP '2026-10-16 08:00:00'), "
    "(2, -3, 12, 2.25, 'south', DATE '2026-10-17', TIMESTAMP '2026-10-17 09:30:00'), "
    "(3, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(3,)]

What it does

can_cast_implicitly(source, target) answers one question about two types: would the VAST Query Engine convert a value of source's type to target's type by itself, where the types meet, for example in a comparison or in coalesce? Conversions that widen a value convert implicitly; conversions that could lose information don't:

The arguments' values don't change the result: can_cast_implicitly(1::INTEGER, 300::INTEGER) is true, and so is can_cast_implicitly(1::INTEGER, 1::BIGINT). To see an expression's type, use typeof.

NULLs and edge cases

A NULL literal has a type of its own that converts to any type, so it's true as a source; no type converts to it, so it's false as a target.

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

run("SELECT can_cast_implicitly(NULL, 1), can_cast_implicitly(1, NULL), can_cast_implicitly(NULL, NULL)")
run("SELECT can_cast_implicitly(1::INTEGER, 'a'), can_cast_implicitly('a', 1::INTEGER), "
    "can_cast_implicitly('a', '\\xAA'::BLOB), can_cast_implicitly('\\xAA'::BLOB, 'a')")
run("SELECT can_cast_implicitly(true, 1), can_cast_implicitly(1, true), "
    "can_cast_implicitly(1::UTINYINT, 1::TINYINT), can_cast_implicitly(1::TINYINT, 1::UTINYINT), "
    "can_cast_implicitly('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID, 'a')")
run("SELECT can_cast_implicitly(1)")

[(True, False, True)]
[(False, False, False, False)]
[(False, False, False, False, False)]
InternalError: Binder Error: No function matches the given name and argument types 'can_cast_implicitly(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The conversions in What it does, as the cluster returns them:

run("SELECT can_cast_implicitly(1::INTEGER, 1::BIGINT), can_cast_implicitly(1::BIGINT, 1::INTEGER), "
    "can_cast_implicitly(1::INTEGER, 1::HUGEINT), can_cast_implicitly(1::INTEGER, 1::DOUBLE), "
    "can_cast_implicitly(1::DOUBLE, 1::INTEGER)")
run("SELECT can_cast_implicitly(1.5::FLOAT, 1.5::DOUBLE), can_cast_implicitly(1.5::DOUBLE, 1.5::FLOAT), "
    "can_cast_implicitly(1::INTEGER, 1.5), can_cast_implicitly([1], [1.5])")
run("SELECT can_cast_implicitly(DATE '2026-10-16', TIMESTAMP '2026-10-16'), "
    "can_cast_implicitly(TIMESTAMP '2026-10-16', DATE '2026-10-16'), "
    "can_cast_implicitly(1::INTEGER, 300::INTEGER), typeof(can_cast_implicitly(1, 1))")

[(True, False, True, True, False)]
[(True, False, True, True)]
[(True, False, True, 'BOOLEAN')]

The same checks on the table's columns. The result is the same for every row, including the row of NULLs, because only the column types count:

run(f"SELECT id, can_cast_implicitly(small, big), can_cast_implicitly(big, small), "
    f"can_cast_implicitly(small, ratio), can_cast_implicitly(day, at), can_cast_implicitly(small, label) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, False, True, True, False), (2, True, False, True, True, False), (3, True, False, True, True, False)]

INTEGER doesn't convert to VARCHAR implicitly, so a function that has to combine the two, such as coalesce, fails until you cast one of them:

run(f"SELECT coalesce(small, label) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, coalesce(CAST(small AS VARCHAR), label) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: Cannot mix values of type INTEGER and VARCHAR in COALESCE operator - an explicit cast is required
[(1, '7'), (2, '-3'), (3, None)]

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). can_cast_implicitly depends only on the types of its arguments, which are known before any row is read, so it's computed once while the query is planned. A condition on it leaves no FILTER step in the plan:

plan(f"SELECT id FROM {TABLE} WHERE (can_cast_implicitly(small, big)) IS NOT NULL")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• It costs nothing per row. Because it's computed while the query is planned, using it on a column doesn't make the scan read more data or add work for each row.
• Use it to find the cast a query needs. Before you combine two columns in coalesce, CASE or greatest, check whether their types convert. If they don't, add a CAST to one of them.

Limitations and errors

• Exactly two arguments. Any other number fails with Binder Error: No function matches the given name and argument types, for example 'can_cast_implicitly(INTEGER_LITERAL)'. The error lists the one form it takes: can_cast_implicitly(ANY, ANY) -> BOOLEAN.
• Only implicit conversions. false doesn't mean that the conversion is impossible: CAST('7' AS INTEGER) works, although can_cast_implicitly('a', 1::INTEGER) is false. It means that you have to write the CAST.

Similar functions

run("SELECT typeof('7'::INTEGER), CAST('7' AS INTEGER), TRY_CAST('x' AS INTEGER), pg_typeof(1), "
    "can_cast_implicitly(1::INTEGER, 1::BIGINT)")

[('INTEGER', 7, None, 'integer', True)]

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 conditional and conversion functions.
• Data types in SQL: type names and conversion rules.
• EXPLAIN: how to read a plan.