CREATE TABLE

Applies to VAST Cluster 5.5.

Creates a table, a sorted table or a vector-indexed table.

Syntax

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] "<bucket>/<schema>".<table> (
    <column> <type> [, ...]
    [, CONSTRAINT <constraint-name> SORT KEY (<column> [, ...])]
)
[VECTOR CLUSTER BY <vector-column> (metric = '<metric>' [, ...])]

Parameters and rules

• A column has a name and a type, and nothing else: NOT NULL, PRIMARY KEY, UNIQUE, CHECK, DEFAULT and generated columns are all refused. SORT KEY is the only constraint.
• CREATE TABLE ... AS SELECT isn't supported. Create the table, then fill it with INSERT ... SELECT.
• Some types are accepted here but can't be read through the Query Engine: MAP(...) and TIMESTAMP WITH TIME ZONE. TIME WITH TIME ZONE and fixed-size arrays such as INTEGER[4] are refused. See data types in SQL.
• The Query Engine can't create partitioned tables. Create them with the Python SDK, Trino or Spark, and query them there: the Query Engine refuses every statement on a partitioned table (not supported).
• Plan sorting and indexing before you create the table. Sorting can't be removed, and some table structures can't be combined; see limits.

Sorted tables

A sort key orders the table's data by its columns, in the background, so that filters on those columns skip more data. Choose the columns your queries filter on most (data modeling). You can also add a sort key to an existing table with ALTER TABLE.

Vector-indexed tables

You create a vector index when you create the table, with VECTOR CLUSTER BY on a VECTOR(FLOAT, <dim>) or VECTOR(FLOAT16, <dim>) column. You can't add one later. See vector search SQL.

What happens on the cluster

CREATE TABLE is a change to the database's catalog. It writes no rows, and nothing is computed in the Query Engine.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, creates the schema docs_sql_create_table and a table in it, runs the same statement again with IF NOT EXISTS, and lists the table's columns:

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

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}")
COLUMNS = "id BIGINT, city VARCHAR, temperature DOUBLE, scores INTEGER[], location STRUCT(lat DOUBLE, lon DOUBLE)"
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.readings ({COLUMNS})")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.readings ({COLUMNS})")
run(f"DESCRIBE {SCHEMA}.readings")

[]
[]
[]
[('id', 'BIGINT', 'YES', None, None, None), ('city', 'VARCHAR', 'YES', None, None, None), ('temperature', 'DOUBLE', 'YES', None, None, None), ('scores', 'INTEGER[]', 'YES', None, None, None), ('location', 'STRUCT(lat DOUBLE, lon DOUBLE)', 'YES', None, None, None)]

A sorted table. On a sorted table the rowid pseudo-column is a DECIMAL(38,0) value, not the UBIGINT of a plain table:

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.readings_sorted "
    "(sensor VARCHAR, ts TIMESTAMP, temperature DOUBLE, "
    "CONSTRAINT readings_sk SORT KEY (sensor, ts))")
run(f"INSERT INTO {SCHEMA}.readings_sorted VALUES ('s1', TIMESTAMP '2026-01-01 10:00:00', 21.5)")
run(f"SELECT typeof(rowid) FROM {SCHEMA}.readings_sorted LIMIT 1")

[]
[(1,)]
[('DECIMAL(38,0)',)]

Column constraints, defaults and CREATE TABLE ... AS SELECT are refused:

run(f"CREATE TABLE {SCHEMA}.c1 (id BIGINT NOT NULL)")
run(f"CREATE TABLE {SCHEMA}.c2 (id BIGINT DEFAULT 0)")
run(f"CREATE TABLE {SCHEMA}.c3 (id BIGINT, id2 BIGINT GENERATED ALWAYS AS (id * 2))")
run(f"CREATE TABLE {SCHEMA}.c4 AS SELECT id, city FROM {SCHEMA}.readings")

InternalError: internal error: This functionality is not implemented yet (specific message not set)
InternalError: internal error: This functionality is not implemented yet (specific message not set)
InternalError: Cannot create column "id2": Generated columns are not supported in this version (possibly more generated columns not reported)
InternalError: Not implemented Error: not supported

Instead of CREATE TABLE ... AS SELECT, create the table and copy the rows with INSERT ... SELECT:

run(f"INSERT INTO {SCHEMA}.readings VALUES (1, 'Lisbon', 21.5, [1, 2], {{'lat': 38.7, 'lon': -9.1}})")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.cities (id BIGINT, city VARCHAR)")
run(f"INSERT INTO {SCHEMA}.cities SELECT id, city FROM {SCHEMA}.readings")
run(f"SELECT id, city FROM {SCHEMA}.cities")

[(1,)]
[]
[(1,)]
[(1, 'Lisbon')]

Clean up:

for table in ("readings", "readings_sorted", "cities"):
    run(f"DROP TABLE IF EXISTS {SCHEMA}.{table}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]
[]
[]

Errors

The class is InternalError.

The status 58 message doesn't say which part of the statement was refused. Remove the constraints and defaults, and check your application's values before you insert them instead.

Trino and Spark

Trino and Spark create tables with their own CREATE TABLE statements, which also create partitioned tables and support CREATE TABLE ... AS SELECT; sorting is set differently in each. See Trino and Spark.

See also: ALTER TABLE, DESCRIBE, data modeling.