typeof

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Converting types. Returns the name of the type of an expression, as text: 'INTEGER', 'DECIMAL(2,1)', 'VARCHAR[]'. Use it to check what type a column, a literal or a calculation has before you compare or convert it.

Syntax

typeof(x)

Returns: VARCHAR, the type name in uppercase, with its parameters: DECIMAL(10,2), INTEGER[2], STRUCT(a INTEGER).

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_typeof. 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 with one column of each of several types and two rows. The second 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_typeof"'

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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, n INTEGER, price DOUBLE, "
    "amount DECIMAL(10,2), ok BOOLEAN, day DATE, at TIMESTAMP, u UUID, tags BIGINT[], data BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 42, 2.5, 2.50, true, DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00', "
    "'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f', [1, 2], '\\x68\\x69'::BLOB), "
    "(2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(2,)]

What it does

typeof(x) returns the type the VAST Query Engine gives x. It looks only at the type, not the value:

• A column has its declared type on every row, including rows where it's NULL.
• A literal's type follows its form. 1 is an INTEGER, 2147483648 a BIGINT, 1.5 a DECIMAL(2,1) (not a DOUBLE), 1e3 a DOUBLE, and 'a' a VARCHAR.
• Calculations have the type the operator returns. 1 + 1.5 is a DECIMAL(12,1), 1 + 1.5::DOUBLE a DOUBLE, 7 / 2 a DOUBLE, and the sum of two TINYINT values a TINYINT.
• Parameters are part of the name: DECIMAL(10,2), INTEGER[2] for a fixed-size array, STRUCT(a INTEGER), MAP(VARCHAR, INTEGER), ENUM('a', 'b'). A length on VARCHAR isn't kept: typeof('abc'::VARCHAR(1)) is 'VARCHAR'.

NULLs and edge cases

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

run("SELECT typeof(NULL), typeof(CAST(NULL AS INTEGER)), typeof(1), typeof(2147483648), "
    "typeof(9223372036854775808), typeof(1.5), typeof(1e3), typeof(1.5::DOUBLE)")
run("SELECT typeof(true), typeof(DATE '2026-10-16'), typeof(TIMESTAMP '2026-10-16'), typeof(INTERVAL 1 DAY), "
    "typeof([1, 2]), typeof([1, 2]::INTEGER[2]), typeof({'a': 1}), typeof(MAP {'a': 1})")
run("SELECT typeof('a'::BLOB), typeof('1'::BIT), typeof('{}'::JSON), typeof(gen_random_uuid()), "
    "typeof(typeof(1))")
run("SELECT typeof(1, 2)")

[('"NULL"', 'INTEGER', 'INTEGER', 'BIGINT', 'HUGEINT', 'DECIMAL(2,1)', 'DOUBLE', 'DOUBLE')]
[('BOOLEAN', 'DATE', 'TIMESTAMP', 'INTERVAL', 'INTEGER[]', 'INTEGER[2]', 'STRUCT(a INTEGER)', 'MAP(VARCHAR, INTEGER)')]
[('BLOB', 'BIT', 'JSON', 'UUID', 'VARCHAR')]
InternalError: Binder Error: No function matches the given name and argument types 'typeof(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The type of each column. It's the same on the row where every value is NULL:

run(f"SELECT id, typeof(name), typeof(n), typeof(price), typeof(amount), typeof(ok), typeof(day), typeof(at), "
    f"typeof(u), typeof(tags), typeof(data) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'VARCHAR', 'INTEGER', 'DOUBLE', 'DECIMAL(10,2)', 'BOOLEAN', 'DATE', 'TIMESTAMP', 'UUID', 'BIGINT[]', 'BLOB'), (2, 'VARCHAR', 'INTEGER', 'DOUBLE', 'DECIMAL(10,2)', 'BOOLEAN', 'DATE', 'TIMESTAMP', 'UUID', 'BIGINT[]', 'BLOB')]

Check the type of a calculation before you compare it. Adding a DECIMAL literal keeps the result a DECIMAL; a DOUBLE makes it a DOUBLE; dividing two integers with / gives a DOUBLE:

run("SELECT typeof(1 + 1.5), typeof(1 + 1.5::DOUBLE), typeof(7 / 2), typeof(1::TINYINT + 1::TINYINT), "
    "typeof('abc'::VARCHAR(1)), typeof('a'::ENUM('a', 'b'))")

[('DECIMAL(12,1)', 'DOUBLE', 'DOUBLE', 'TINYINT', 'VARCHAR', "ENUM('a', 'b')")]

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). typeof() depends only on types, which the Query Engine knows when it plans the query, so it never needs the column's values:

• In WHERE, a condition such as typeof(n) = 'INTEGER' is decided before the scan. The plan has no FILTER step for it.
• In the SELECT list, it gives the same text for every row.

The plan of a condition on typeof():

plan(f"SELECT id FROM {TABLE} WHERE typeof(n) = 'INTEGER'")

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

Make it faster

• Use it while you write a query, not in production filters. A condition on typeof() is true for every row or for none, so it doesn't select rows; it only helps you see which type a comparison or a conversion works on.

Limitations and errors

• Exactly one argument. typeof() and typeof(1, 2) fail with No function matches the given name and argument types.
• typeof(NULL) includes quotes. It returns "NULL", with the double quotes. A NULL of a known type, such as CAST(NULL AS INTEGER) or a NULL in a column, gives that type instead.
• The names are uppercase and exact. Compare with 'INTEGER', not 'integer', and include parameters: a DECIMAL(10,2) column gives 'DECIMAL(10,2)', not 'DECIMAL'. Use starts_with(typeof(x), 'DECIMAL') to match any precision.

run("SELECT typeof(NULL) = 'NULL', typeof(NULL) = '\"NULL\"', length(typeof(NULL))")
run(f"SELECT typeof(amount) = 'DECIMAL', starts_with(typeof(amount), 'DECIMAL') FROM {TABLE} LIMIT 1")

[(False, True, 6)]
[(False, True)]

Similar functions

run("SELECT typeof(1), pg_typeof(1), typeof(1.5), pg_typeof(1.5), pg_typeof(1) = typeof(1), "
    "can_cast_implicitly(1::INTEGER, 1::BIGINT), typeof(CAST(1 AS DOUBLE))")

[('INTEGER', 'integer', 'DECIMAL(2,1)', 'decimal(2,1)', False, True, 'DOUBLE')]

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.
• Data types in SQL: the types and their names.
• EXPLAIN: how to read a plan.