--- title: "can_cast_implicitly" slug: "dev-database-reference-sql-functions-conditional-and-cast-can-cast-implicitly" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-can-cast-implicitly" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # can_cast_implicitly *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › 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) ``` | Argument | Type | Description | | --- | --- | --- | | `source` | any | A value whose type is converted from. Its value is ignored. | | `target` | any | A value whose type is converted to. Its value is ignored. | **Returns:** `BOOLEAN`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce)? Conversions that widen a value convert implicitly; conversions that could lose information don't: | Conversion | Result | | --- | --- | | `INTEGER` to `BIGINT`, `HUGEINT` or `DOUBLE` | `true` | | `BIGINT` to `INTEGER`, `DOUBLE` to `INTEGER` | `false` | | `FLOAT` to `DOUBLE` | `true` | | `DOUBLE` to `FLOAT` | `false` | | `INTEGER` to a decimal literal such as `1.5` (a `DECIMAL`) | `true` | | `DATE` to `TIMESTAMP` | `true` | | `TIMESTAMP` to `DATE` | `false` | | `INTEGER[]` to a list of decimals | `true` | 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-typeof). ## NULLs and edge cases | Input | Result | | --- | --- | | `can_cast_implicitly(NULL, 1)` (a NULL literal as source) | `true` | | `can_cast_implicitly(1, NULL)` (a NULL literal as target) | `false` | | `can_cast_implicitly(NULL, NULL)` | `true` | | `INTEGER` to `VARCHAR`, `VARCHAR` to `INTEGER` | `false` both ways | | `VARCHAR` to `BLOB`, `BLOB` to `VARCHAR` | `false` both ways | | `BOOLEAN` to `INTEGER`, `INTEGER` to `BOOLEAN` | `false` both ways | | `UTINYINT` to `TINYINT`, `TINYINT` to `UTINYINT` | `false` both ways | | `UUID` to `VARCHAR` | `false` | | One argument | Error: `No function matches the given name and argument types 'can_cast_implicitly(INTEGER_LITERAL)'` | 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-can-cast-implicitly#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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-can-cast-implicitly#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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce), [CASE](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case) or [greatest](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-greatest), check whether their types convert. If they don't, add a [CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-cast). ## Similar functions | Function | What it tells you | Example | Result | | --- | --- | --- | --- | | [typeof](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-typeof) | the name of an expression's type | `typeof('7'::INTEGER)` | `'INTEGER'` | | [CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-cast) | converts a value; fails if it doesn't convert | `CAST('7' AS INTEGER)` | `7` | | [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast) | converts a value; NULL if it doesn't convert | `TRY_CAST('x' AS INTEGER)` | `NULL` | | [pg_typeof](/develop/docs/dev-database-reference-sql-functions-utility-pg-typeof) | the name of an expression's type | `pg_typeof(1)` | `'integer'` | ``` 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conditional and conversion functions. - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): type names and conversion rules. - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.