--- title: "Data types in SQL" slug: "dev-database-reference-sql-data-types" updated: 2026-10-30T18:19:11Z published: 2026-10-30T18:19:11Z canonical: "docs.vastdata.com/dev-database-reference-sql-data-types" --- > ## 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. # 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](/develop/docs/dev-database-reference-data-types). ## Type names | SQL type | VAST DataBase type | Through the Query Engine | | --- | --- | --- | | `BOOLEAN` | BOOL | Create, write, read | | `INTEGER`, `BIGINT` | INT32, INT64 | Create, write, read | | `TINYINT` | INT8 | Used in vector-index examples | | `DOUBLE` | FLOAT64 | Create, write, read | | `VARCHAR` | STRING | Create, write, read | | `DATE` | DATE32 | Create, write, read | | `TIMESTAMP` | TIMESTAMP | Create, write, read | | `UUID` | UUID | Create, write, read; `gen_random_uuid()` makes values | | `<type>[]`, for example `INTEGER[]` | ARRAY (list) | Create, write, read | | `STRUCT(<field> <type>, ...)` | ROW (struct) | Create, write, read; filter on a field | | `VECTOR(FLOAT, <dim>)`, `VECTOR(FLOAT16, <dim>)` | VECTOR | Create, write, read, index; see [vector search SQL](/develop/docs/dev-database-reference-sql-vector-search) | | `BLOB` | BINARY | Read and compare: the `key` and `value` columns of [topic tables](/develop/docs/dev-event-broker-guides-querying-topics) | | `MAP(<key type>, <value type>)` | MAP | Create and write only: selecting the column fails | | `TIMESTAMP WITH TIME ZONE` (`TIMESTAMPTZ`) | TIMESTAMP WITH TZ | Create, insert and count only: selecting the column fails | | `TIME WITH TIME ZONE` | — | Refused by `CREATE TABLE` | | Fixed-size arrays, such as `INTEGER[4]` | — | Refused by `CREATE TABLE`; use `VECTOR(...)` or a list ([vector search SQL](/develop/docs/dev-vector-search-reference-sql#vector-column-types)) | `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](/develop/docs/dev-database-reference-sql-functions-index) 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](/develop/docs/dev-database-reference-data-types#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](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-event-broker-guides-querying-topics)), and vectors as `[1, 2, 3]::FLOAT[3]` ([vector search SQL](/develop/docs/dev-database-reference-sql-vector-search)). - **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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction). 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, "") 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`. | You wrote | Message | | --- | --- | | A query that returns a `MAP` column | `Unsupported column type 'MAP(VARCHAR, INTEGER)' of column '<column>' in 'VAST_SCAN '.` | | A query that returns a `TIMESTAMP WITH TIME ZONE` column | `Unsupported column type 'TIMESTAMP WITH TIME ZONE' of column '<column>' in 'VAST_SCAN '.` | | A `TIME WITH TIME ZONE` column in `CREATE TABLE` | `Binder Error: TIME WITH TIME ZONE type is not supported in table definitions. Column "<column>" has type: TIME WITH TIME ZONE` | | `now() - INTERVAL ...` | `Binder Error: No function matches the given name and argument types '-(TIMESTAMP WITH TIME ZONE, INTERVAL)'. ...` Cast `now()` to `TIMESTAMP` first. | ## 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](/develop/docs/dev-database-reference-data-types#type-mapping) and its [engine differences](/develop/docs/dev-database-reference-data-types#engine-differences). See also: [data types](/develop/docs/dev-database-reference-data-types), [CREATE TABLE](/develop/docs/dev-database-reference-sql-create-table).