Data types in SQL

Applies to VAST Cluster 5.5.

This page lists the SQL type names you use in VAST Query Engine statements, which VAST DataBase type each one creates, and the types the Query Engine can't read. For how the same types appear in Arrow, Trino, Spark and the Python SDK, see data types.

Type names

INTEGER, REAL, DECIMAL(<p>, <s>), DATE, TIME and the TIMESTAMP precisions TIMESTAMP_S, TIMESTAMP_MS and TIMESTAMP_NS columns can also be created, written and read through the Query Engine; the function pages use them. An INTERVAL column is refused by CREATE TABLE. Python's fetchall() can't convert a TIMESTAMP_NS value with nanoseconds, so cast it to VARCHAR to read it. Other types in the type mapping, such as SMALLINT and the unsigned integers, aren't covered here.

Rules

• Columns you can't read. A MAP or TIMESTAMP WITH TIME ZONE column can be created, and the table can be written and counted, but a query that returns the column fails with Unsupported column type. The table's other columns can still be selected, so name them instead of SELECT *. DESCRIBE lists these columns anyway.
• Store times in UTC. Use TIMESTAMP, not a time-zone type. now() and current_timestamp return a TIMESTAMP WITH TIME ZONE value; cast it with CAST(now() AS TIMESTAMP) to compare it with a TIMESTAMP column. The cast is computed once, so the comparison is still pushed down.
• Literals. 'text', TIMESTAMP '2026-01-01 10:00:00', DATE '2026-01-01', TIMESTAMPTZ '2026-01-01 10:00:00+00', a list [1, 2] and a struct {'lat': 38.7, 'lon': -9.1}. Binary values are written as casts, such as 'order-1'::BLOB (topic tables), and vectors as [1, 2, 3]::FLOAT[3] (vector search SQL).
• Large integer results. count_if() and sum() of a BOOLEAN return a 128-bit integer (HUGEINT). Cast the result to BIGINT if your client needs a 64-bit integer.
• rowid is a UBIGINT on a plain table and a DECIMAL(38,0) on a sorted table.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, creates the schema docs_sql_data_types and a table with several types, and reads a row back:

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_data_types"'

def connect(autocommit=True):
    return 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=autocommit,
    )

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)

conn = connect()
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.samples "
    "(id BIGINT, ok BOOLEAN, name VARCHAR, reading DOUBLE, day DATE, taken_at TIMESTAMP, "
    "scores INTEGER[], location STRUCT(lat DOUBLE, lon DOUBLE), uid UUID)")
run(f"""INSERT INTO {SCHEMA}.samples VALUES (1, true, 'Lisbon', 21.5, DATE '2026-01-01',
    TIMESTAMP '2026-01-01 10:00:00', [1, 2], {{'lat': 38.7, 'lon': -9.1}}, gen_random_uuid())""")
run(f"SELECT id, ok, name, reading, day, taken_at, scores, location, typeof(uid) FROM {SCHEMA}.samples")

[]
[]
[(1,)]
[(1, True, 'Lisbon', 21.5, datetime.date(2026, 1, 1), datetime.datetime(2026, 1, 1, 10, 0), [1, 2], {'lat': 38.7, 'lon': -9.1}, 'UUID')]

A TIMESTAMP WITH TIME ZONE column can be written and counted, but not read. A TIME WITH TIME ZONE column can't be created:

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.zoned (id INTEGER, t TIMESTAMPTZ)")
run(f"INSERT INTO {SCHEMA}.zoned VALUES (1, TIMESTAMPTZ '2026-01-01 10:00:00+00')")
run(f"SELECT count(*) FROM {SCHEMA}.zoned")
run(f"SELECT id FROM {SCHEMA}.zoned")
run(f"SELECT * FROM {SCHEMA}.zoned")
run(f"CREATE TABLE {SCHEMA}.zoned_time (id INTEGER, t TIME WITH TIME ZONE)")

[]
[(1,)]
[(1,)]
[(1,)]
InternalError: Unsupported column type 'TIMESTAMP WITH TIME ZONE' of column 't' in 'VAST_SCAN '.
InternalError: Binder Error: TIME WITH TIME ZONE type is not supported in table definitions. Column "t" has type: TIME WITH TIME ZONE

The same for a MAP column, and the way to compare a timestamp with now:

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.attrs (id BIGINT, m MAP(VARCHAR, INTEGER))")
run(f"SELECT id FROM {SCHEMA}.attrs")
run(f"SELECT * FROM {SCHEMA}.attrs")
run(f"SELECT id FROM {SCHEMA}.samples WHERE taken_at > CAST(now() AS TIMESTAMP) - INTERVAL 10000 DAY")

[]
[]
InternalError: Unsupported column type 'MAP(VARCHAR, INTEGER)' of column 'm' in 'VAST_SCAN '.
[(1,)]

Clean up:

for table in ("samples", "zoned", "attrs"):
    run(f"DROP TABLE IF EXISTS {SCHEMA}.{table}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]
[]
[]

Errors

The class is InternalError.

Trino and Spark

Trino and Spark have their own type names, with map and timestamp types of their own and no vector type. See the type mapping and its engine differences.

See also: data types, CREATE TABLE.