DROP TABLE

Applies to VAST Cluster 5.5.

Drops a table and its data.

Syntax

DROP TABLE [IF EXISTS] "<bucket>/<schema>".<table>

Parameters and rules

• Dropping a table can't be undone, except from a snapshot that your platform team took before; see snapshots and replication.
• To remove a vector index, drop the table: there's no statement that drops only the index (vector search SQL).
• You can't drop a table through a snapshot path: snapshots are read-only.

What happens on the cluster

DROP TABLE removes the table from the database's catalog. Nothing is computed in the Query Engine. For when the space comes back, see write path.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, creates the schema docs_sql_drop_table with a table, drops it, and drops it again with and without IF EXISTS:

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_drop_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}.scratch (id BIGINT)")
run(f"DROP TABLE {SCHEMA}.scratch")
run(f"DROP TABLE IF EXISTS {SCHEMA}.scratch")
run(f"DROP TABLE {SCHEMA}.scratch")

[]
[]
[]
[]
InternalError: Catalog Error: Table with name scratch does not exist!

Clean up:

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

[]

Errors

Trino and Spark

Trino and Spark drop tables with their own DROP TABLE statements, through their own catalogs; see Trino and Spark.

See also: CREATE TABLE, DROP SCHEMA, DELETE and TRUNCATE to empty a table without dropping it.