--- title: "CREATE TABLE" slug: "dev-database-reference-sql-create-table" updated: 2026-10-30T18:19:11Z published: 2026-10-30T18:19:11Z canonical: "docs.vastdata.com/dev-database-reference-sql-create-table" --- > ## 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. # 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] "/". ( [, ...] [, CONSTRAINT SORT KEY ( [, ...])] ) [VECTOR CLUSTER BY (metric = '' [, ...])] ``` ## Parameters and rules | Part | Meaning | | --- | --- | | `"<bucket>/<schema>".<table>` | The full table name. The schema must exist. | | `OR REPLACE` | If the table exists, replace it with the new, empty one. | | `IF NOT EXISTS` | If the table exists, do nothing. The existing table isn't checked against your column list. | | `<column> <type>` | A column name and its SQL type. See [data types in SQL](/develop/docs/dev-database-reference-sql-data-types). | | `CONSTRAINT <name> SORT KEY (...)` | Makes the table a [sorted table](/docs-5-5/docs/sorted-tables), sorted on up to 4 columns, in the order you list them. | | `VECTOR CLUSTER BY` | Creates a vector index on one `VECTOR(...)` column. The grammar and its rules are in [vector search SQL](/develop/docs/dev-database-reference-sql-vector-search). | - 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](/develop/docs/dev-database-reference-sql-insert). - 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](/develop/docs/dev-database-reference-sql-data-types). - The Query Engine can't create partitioned tables. Create them with the [Python SDK](/develop/docs/dev-database-reference-sdk#partitions), Trino or Spark, and query them there: the Query Engine refuses every statement on a partitioned table ([not supported](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-database-reference-limits#combining-table-structures). ### 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](/develop/docs/dev-database-guides-data-modeling#sorted-tables)). You can also add a sort key to an existing table with [ALTER TABLE](/develop/docs/dev-database-reference-sql-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](/develop/docs/dev-database-reference-sql-vector-search). ## 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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction). 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, "") 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`. | You wrote | Message | | --- | --- | | `NOT NULL`, `PRIMARY KEY`, `UNIQUE`, `CHECK (...)` or `DEFAULT` | `internal error: This functionality is not implemented yet (specific message not set)` (status 58) | | A generated column | `Cannot create column "<column>": Generated columns are not supported in this version (possibly more generated columns not reported)` (status 58) | | `CREATE TABLE ... AS SELECT` | `Not implemented Error: not supported` | | A `TIME WITH TIME ZONE` column | `Binder Error: TIME WITH TIME ZONE type is not supported in table definitions. Column "<column>" has type: TIME WITH TIME ZONE` | | A fixed-size array column, such as `INTEGER[4]` | `Binder Error: ARRAY type is not supported in table definitions. Column "<column>" has type: INTEGER[4]` ([vector search SQL](/develop/docs/dev-vector-search-reference-sql#errors-youll-see)) | 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](/develop/docs/dev-database-integrations-trino#creating-tables-and-writing-data) and [Spark](/develop/docs/dev-database-integrations-spark#spark-sql-and-dataframes). See also: [ALTER TABLE](/develop/docs/dev-database-reference-sql-alter-table), [DESCRIBE](/develop/docs/dev-database-reference-sql-describe), [data modeling](/develop/docs/dev-database-guides-data-modeling).