DELETE and TRUNCATE

Applies to VAST Cluster 5.5.

DELETE deletes the rows that match a condition. TRUNCATE deletes every row of a table, and keeps the table.

Syntax

DELETE FROM "<bucket>/<schema>".<table> [WHERE <condition>]
TRUNCATE [TABLE] "<bucket>/<schema>".<table>

Parameters and rules

Part	Meaning
`WHERE <condition>`	Which rows to delete. Any condition that works in a SELECT on one table works here. Without `WHERE`, every row is deleted.
`TRUNCATE [TABLE]`	Deletes every row. `TRUNCATE TABLE <table>` and `TRUNCATE <table>` both work.

Both statements return one row with one column, Count: the number of rows deleted.
To delete rows that match another table, query that table first, and put the values in the WHERE clause: a subquery such as IN (SELECT ...) needs a join, which isn't supported. Mind the IN list rule.
The Query Engine can't delete from a partitioned table, so it can't drop partitions either. Use the Python SDK, Trino or Spark (updates and deletes).
You can't delete through a snapshot path: snapshots are read-only.

What happens on the cluster

The Query Engine finds the rows that match WHERE, following the same rules as a SELECT, and VAST DataBase marks them as deleted. Only the count comes back. Deleted rows stop being visible when the transaction commits, but their space is freed later, in the background (write path).

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_delete with four rows:

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

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()
TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, city VARCHAR, temperature DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES (1, 'Lisbon', 21.5), (2, 'Oslo', 4.0), (3, 'Cairo', 30.2), (4, 'Lima', NULL)")

[]
[]
[(4,)]

Delete with a condition, then empty the table with TRUNCATE:

run(f"DELETE FROM {TABLE} WHERE temperature < 10 OR temperature IS NULL")
run(f"SELECT id, city FROM {TABLE} ORDER BY id LIMIT 10")
run(f"TRUNCATE TABLE {TABLE}")
run(f"SELECT count(*) FROM {TABLE}")

[(2,)]
[(1, 'Lisbon'), (3, 'Cairo')]
[(2,)]
[(0,)]

Clean up:

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

[]
[]

Errors

No DELETE or TRUNCATE errors are listed here. For the conditions the Query Engine refuses, such as subqueries and long IN lists, see the SELECT errors.

Trino and Spark

Trino and Spark delete with their own DELETE statements, and can also delete whole partitions; see updates and deletes.