ALTER TABLE

Applies to VAST Cluster 5.5.

Renames a table, adds, renames and drops columns, and adds a sort key.

Syntax

ALTER TABLE "<bucket>/<schema>".<table> RENAME TO "<bucket>/<schema>.<new-table>"
ALTER TABLE "<bucket>/<schema>".<table> ADD [COLUMN] <column> <type>
ALTER TABLE "<bucket>/<schema>".<table> RENAME COLUMN <column> TO <new-column>
ALTER TABLE "<bucket>/<schema>".<table> DROP [COLUMN] <column>
ALTER TABLE "<bucket>/<schema>".<table> DROP [COLUMN] "<struct-column>.<field>"
ALTER TABLE "<bucket>/<schema>".<table> ADD CONSTRAINT <constraint-name> SORT KEY (<column> [, ...])

Parameters and rules

• You can't change a column's type or default: ALTER COLUMN ... SET DATA TYPE and ALTER COLUMN ... SET DEFAULT fail. To change a type, add a new column, copy the values into it with UPDATE and a CAST, then drop the old column.
• ADD CONSTRAINT accepts only SORT KEY. A PRIMARY KEY fails.
• A sort key can't be removed or changed once it's set, and some table structures can't be sorted, such as a table with a semi-sorted projection or user-defined row IDs. Check limits before you add one. DESCRIBE doesn't show the sort key.
• On a vector-indexed table you can't add or drop columns, or rename the indexed column; see vector search SQL.
• The Query Engine can't alter a partitioned table; see not supported.

What happens on the cluster

ALTER TABLE changes the table's definition in the database's catalog. Adding or dropping a column doesn't rewrite the existing rows (write path). Adding a sort key starts background sorting of the table's data on the cluster (write path). 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, and creates the schema docs_sql_alter_table with a small table:

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_alter_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}")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.readings (id BIGINT, city VARCHAR)")
run(f"INSERT INTO {SCHEMA}.readings VALUES (1, 'Lisbon'), (2, 'Oslo')")

[]
[]
[(2,)]

Add a column, rename it, and drop it again:

run(f"ALTER TABLE {SCHEMA}.readings ADD COLUMN temp INTEGER")
run(f"ALTER TABLE {SCHEMA}.readings RENAME COLUMN temp TO temperature")
run(f"SELECT id, city, temperature FROM {SCHEMA}.readings ORDER BY id LIMIT 10")
run(f"ALTER TABLE {SCHEMA}.readings DROP COLUMN temperature")

[]
[]
[(1, 'Lisbon', None), (2, 'Oslo', None)]
[]

Add a struct column, drop one of its fields, and look at the result:

run(f"ALTER TABLE {SCHEMA}.readings ADD COLUMN location STRUCT(lat DOUBLE, lon DOUBLE, alt DOUBLE)")
run(f'ALTER TABLE {SCHEMA}.readings DROP COLUMN "location.alt"')
run(f"DESCRIBE {SCHEMA}.readings")

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

Rename the table. The bare name fails; the full name in one quoted string works:

run(f"ALTER TABLE {SCHEMA}.readings RENAME TO readings_2026")
run(f'ALTER TABLE {SCHEMA}.readings RENAME TO "{BUCKET}/docs_sql_alter_table.readings_2026"')
run(f"SELECT count(*) FROM {SCHEMA}.readings_2026")

InternalError: internal error: Invalid Argument
[]
[(2,)]

Add a sort key to the populated table. Changing a column's type fails:

run(f"ALTER TABLE {SCHEMA}.readings_2026 ADD CONSTRAINT readings_sk SORT KEY (id)")
run(f"ALTER TABLE {SCHEMA}.readings_2026 ALTER COLUMN city SET DATA TYPE VARCHAR")

[]
InternalError: internal error: This functionality is not implemented yet (specific message not set)

Clean up:

run(f"DROP TABLE IF EXISTS {SCHEMA}.readings_2026")
run(f"DROP TABLE IF EXISTS {SCHEMA}.readings")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]
[]

Errors

The class is InternalError.

On a vector-indexed table, ADD COLUMN and DROP COLUMN fail with Invalid Argument, and renaming the indexed column fails with Tried to drop a column which cannot be dropped (vector search SQL).

Trino and Spark

Trino and Spark alter tables with their own statements; in Trino, sorting is a table property. See Trino and Spark, where sorting from Spark is still unconfirmed.

See also: CREATE TABLE, limits, schema changes in the ingest guide.