INSERT

Applies to VAST Cluster 5.5.

Inserts rows into a table, either as values or as the result of a SELECT. To import Parquet files with INSERT, see import Parquet files.

Syntax

INSERT INTO "<bucket>/<schema>".<table> VALUES (<value>, ...) [, (<value>, ...) ...]
INSERT INTO "<bucket>/<schema>".<table> (<column>, ...) VALUES (<value>, ...) [, (<value>, ...) ...]
INSERT INTO "<bucket>/<schema>".<table> [(<column>, ...)] SELECT ...

Parameters and rules

• The statement returns one row with one column, Count: the number of rows inserted.
• Write literals as you would in a SELECT: 'text', TIMESTAMP '2026-01-01 10:00:00', a list [1, 2], a struct {'lat': 38.7, 'lon': -9.1}. See data types in SQL.
• The whole statement must fit the 5 MB statement size limit (limits). To load many rows, use ADBC bulk ingest (cur.adbc_ingest()) instead of long VALUES lists; the profile a query guide loads 200,000 rows that way. Bound parameters (cur.execute(sql, params)) aren't supported (SQL reference).
• There's no upsert: INSERT OR REPLACE, INSERT OR IGNORE and ON CONFLICT fail, because a table has no primary key. To replace rows, DELETE them and INSERT the new ones in one transaction.
• You can't insert into a table in a snapshot, or into a partitioned table (not supported).
• On a sorted table, inserted rows are sorted later, in the background (write path).

What happens on the cluster

The Query Engine evaluates the values, or runs the SELECT, and writes the rows into the table on the cluster. With INSERT ... SELECT, the rows never leave the cluster: only the count comes back to your client (ingest). For what happens to the rows after the commit, see 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_insert with two tables:

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

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

[]
[]
[]

Insert two rows with all columns, then one row with a column list:

run(f"""INSERT INTO {SCHEMA}.readings VALUES
    (1, 'Lisbon', 21.5, [1, 2], {{'lat': 38.7, 'lon': -9.1}}),
    (2, 'Oslo', 4.0, [3], {{'lat': 59.9, 'lon': 10.7}})""")
run(f"INSERT INTO {SCHEMA}.readings (id, city, temperature) VALUES (3, 'Cairo', 30.2)")
run(f"SELECT id, city, temperature, tags, location FROM {SCHEMA}.readings WHERE id < 3 ORDER BY id LIMIT 10")

[(2,)]
[(1,)]
[(1, 'Lisbon', 21.5, [1, 2], {'lat': 38.7, 'lon': -9.1}), (2, 'Oslo', 4.0, [3], {'lat': 59.9, 'lon': 10.7})]

Copy the result of a query into another table:

run(f"INSERT INTO {SCHEMA}.warm SELECT id, city FROM {SCHEMA}.readings WHERE temperature > 20")
run(f"SELECT id, city FROM {SCHEMA}.warm ORDER BY id LIMIT 10")

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

An upsert fails:

run(f"INSERT OR REPLACE INTO {SCHEMA}.warm VALUES (1, 'Lisboa')")

InternalError: Binder Error: There are no UNIQUE/PRIMARY KEY Indexes that refer to this table, ON CONFLICT is a no-op

Clean up:

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

[]
[]
[]

Errors

For a vector-indexed table, see the insert rules in vector search SQL.

Trino and Spark

Trino and Spark insert with their own INSERT statements, and also copy query results with CREATE TABLE ... AS SELECT; Spark writes DataFrames too. See ingest.

See also: import Parquet files, ingest, UPDATE.