Skip to Content
We are live but in Staging 🎉
Data EnginesSQLPostgres compatibilityStatements

Statements

DDL

StatementStatusNotes
CREATE TABLE t (…) [PARTITIONED BY (…)]SupportedInline or table-level PRIMARY KEY, composite keys, NOT NULL, DEFAULT, CHECK, IF NOT EXISTS, OR REPLACE. Partition columns prune scans.
CREATE TABLE t AS SELECT …SupportedTypes inferred from the query; declare columns explicitly when the inferred type is not what you want.
ALTER TABLE t ADD COLUMN c type [DEFAULT …]Supported since 0.1.42Online. The column reads as NULL or its default on every existing row immediately. Several ADD COLUMNs in one statement.
ALTER TABLE t DROP COLUMN cSupportedLogical; no rewrite. Can be mixed with ADD in one statement.
ALTER TABLE t ALTER COLUMN c SET DATA TYPE …PartialWidening only (INTEGER → BIGINT, VARCHAR(10) → VARCHAR(50)). Narrowing is refused.
ALTER TABLE t ADD CONSTRAINT …PartialCHECK is stored and enforced; a FOREIGN KEY added or dropped this way applies from the next statement since 0.1.44; UNIQUE is enforced through a companion index (23505) and applies on every node from the next statement since 0.1.45. See Constraints.
ALTER TABLE t DROP CONSTRAINT nameSupportedRemoves a stored CHECK or FOREIGN KEY; an unknown name is a no-op so migration downgrades run.
REFERENCES … ON DELETE | ON UPDATE CASCADE | SET NULL | SET DEFAULT, DEFERRABLERefused0A000Only immediate, no-action foreign keys can be kept, so only those are accepted. Cascade in the application.
ALTER TABLE … RENAME COLUMN / RENAME TORefused0A000 since 0.1.44ALTER TABLE … RENAME is not supported yet … names the CTAS way around. Recreate with CREATE TABLE new AS SELECT ….
ALTER COLUMN other than SET DATA TYPE (SET DEFAULT, SET NOT NULL, …)Refused0A000 since 0.1.44this ALTER TABLE form is not supported yet … lists the implemented forms. Declare these at CREATE TABLE.
DROP TABLE [IF EXISTS] tSupportedOne table per statement. Drops the data, its indexes and its history.
TRUNCATE [TABLE] tSupportedKeeps schema, key, indexes and graph bindings. One table per statement; TRUNCATE … PARTITION is refused.
CREATE INDEX [name] ON t (col)SupportedNon-unique: exactly one column. Usable the moment the statement returns. since 0.1.41
CREATE UNIQUE INDEX [name] ON t (a, b)Enforced23505Composite allowed; enforced on every write.
CREATE INDEX … USING hnsw | ivfflat (col ops) WITH (…)Supportedpgvector syntax; see Schema changes → Indexes. An unknown WITH key is refused.
CREATE INDEX on an expression, or non-unique on several columnsRefusedMaterialize the expression into a column.
DROP INDEXRefusedAn index is a table named __idx_<table>_<col> (__uidx_… when unique); DROP TABLE it.
CREATE VIEW, CREATE MATERIALIZED VIEWRefused0A000 since 0.1.44views are not supported yet … — use a CTE, or CREATE TABLE … AS SELECT for a materialized result.
CREATE RULERefused42601statement is not part of the tables SQL surface.
CREATE TRIGGER, CREATE OR REPLACE TRIGGER, CREATE CONSTRAINT TRIGGER, CREATE EVENT TRIGGER, CREATE FUNCTION … RETURNS triggerRefused0A000 since 0.1.43See the message below. Before 0.1.43 these failed as a syntax error.
CREATE SEQUENCERefused0A000 since 0.1.44sequences are not supported … — use SERIAL/BIGSERIAL, GENERATED … AS IDENTITY, or a GENERATE_ULID() default, and RETURNING. nextval() itself is 42P01.
CREATE SCHEMA public, SET search_pathAccepted · no effectThere are no schemas; the bucket is the namespace. A leading public. is stripped from every statement. Any other schema name fails to resolve.
CREATE DATABASE [IF NOT EXISTS] name, SHOW DATABASESSupportedRarely needed on the pg wire: the bucket you connected to is the database.
CREATE GRAPH … NODES (…) EDGES (…), DROP GRAPH, SHOW GRAPHSSupportedSRC and DST are mandatory on every edge table. See Graph.
RESTORE TABLE t TO VERSION AS OF n / TO TIMESTAMP AS OF '…Z'SupportedDestructive to writes after the target. See Schema changes → Restore.

The trigger refusal reads:

ERROR:  triggers are not supported yet: a synchronous plpgsql-subset trigger engine
(BEFORE/AFTER row triggers with NEW/OLD, RAISE, and same-transaction INSERT/UPDATE/DELETE)
is on the roadmap. Today, react to writes with an ingest rule → pipeline, or an Action;
validation belongs in CHECK constraints.
SQLSTATE 0A000

Reserved words that commonly collide with column names: at, end, order, table, to, user. Double-quote them everywhere or pick another name (occurred_at).

DML

StatementStatusNotes
INSERT INTO t (cols) VALUES (…), multi-row VALUESSupportedOmitted columns take their default. A column list is optional; without one, values map in table order. A VALUES cell may be a literal, a typed literal (TIMESTAMP '…', DATE '…', 100::BIGINT), now()/CURRENT_TIMESTAMP, or one of the default-expression functions.
VALUES cell with arithmetic (2 + 3) or another function call (upper('a'))Refused42601VALUES expression … Unsupported. Use INSERT INTO t SELECT …, which evaluates anything.
INSERT INTO t SELECT …SupportedColumns map by position.
INSERT of an existing primary keyEnforced23505duplicate key value violates unique constraint. Under concurrent inserts of one key exactly one succeeds. since 0.1.41
INSERT of a child row whose parent does not existEnforced23503violates foreign key constraint. See Constraints → Foreign keys.
INSERT … ON CONFLICT [(cols)] DO NOTHINGSupported since 0.1.43First writer wins under a race; a loser reports INSERT 0 0. The target must be the key or a unique index.
INSERT … ON CONFLICT (cols) DO UPDATE SET …SupportedAssignments take any expression: SET n = n + 1, SET c = EXCLUDED.c, literals.
INSERT … ON CONFLICT ON CONSTRAINT nameRefusedName the columns instead.
INSERT … ON DUPLICATE KEY UPDATE …SupportedThe MySQL spelling, same lowering.
UPSERT INTO t …SupportedInsert-or-replace on the key.
… RETURNING cols | *SupportedOn INSERT, UPDATE, DELETE. Returns the row after defaults.
UPDATE t SET … WHERE …SupportedAny predicate, key or not. Assignments may reference the row’s own columns.
DELETE FROM t WHERE …SupportedAny predicate.
UPDATE / DELETE without WHERERefused42601UPDATE without a WHERE clause is not allowed. Use WHERE TRUE to update every row, or TRUNCATE TABLE ….
UPDATE / MERGE SET target that is not a plain column (SET (a,b) = …, SET t.c = …)Refused
MERGE INTO t USING <table | (SELECT …) | (VALUES …)> ON … WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT …SupportedON must contain a same-name equality per key column, or the key is used.
MERGE with INSERT in WHEN MATCHED, non-INSERT in WHEN NOT MATCHED, or non-literal VALUESRefused
SELECT with joins, CTEs, window functions, set operations, subqueries, LIMIT/OFFSETSupportedDuckDB’s full read surface.
SELECT … FOR UPDATE (and FOR SHARE, FOR NO KEY UPDATE, FOR KEY SHARE)PartialNo lock is taken. Inside a transaction the rows read are guarded at COMMIT (40001 on conflict); outside one it is a snapshot read with NOTICE: row-locking clause outside a transaction has no effect: served as a snapshot read. See Transactions.
SELECT … FROM t VERSION AS OF nSupportedTime travel over committed history.
SELECT … WHERE col = ANY($1)SupportedBind an array parameter instead of building IN (…).

Transactions

StatementStatusNotes
BEGIN, START TRANSACTION, COMMIT, ROLLBACKSupportedMulti-statement, multi-table, atomic at COMMIT.
SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINTSupported
SET TRANSACTION ISOLATION LEVEL …, BEGIN ISOLATION LEVEL …Accepted · no effectThere is one isolation model; see Transactions.
LOCK TABLERefused42601There are no blocking locks. Use FOR UPDATE and retry on 40001.
SELECT pg_advisory_lock(…)Refused42P01The function does not exist.
PREPARE TRANSACTION, two-phase commitRefused42601Not part of the surface.

The contract itself, including what FOR UPDATE guards and how to retry, is on Transactions & concurrency.

Session and utility

StatementStatusNotes
SET statement_timeout = 5000 / TO '5s', RESET statement_timeoutSupportedMilliseconds, or ms/s/min/h/d suffixes; 0 disables. Expiry is 57014.
SET hnsw.ef_search, SET ivfflat.probes, SET dodil.vector_exact_scan, SET dodil.vector_consistencySupportedPer-session vector search knobs.
SET TimeZone, SET TIME ZONEAccepted · NOTICENo effect; timestamps are UTC.
Any other SET / RESET (client_encoding, application_name, DateStyle, …)Accepted · no effect
SHOW server_version, client_encoding, standard_conforming_strings, DateStyle, TimeZone, integer_datetimes, search_pathSupportedFixed answers; see Catalog. Any other SHOW <guc>, including SHOW statement_timeout, is 42P01.
SHOW TABLES, SHOW DATABASES / SHOW SCHEMAS, SHOW VECTOR INDEXES, SHOW GRAPHSSupported
DESCRIBE t / DESC t, DESCRIBE DATABASE dSupportedColumns column, type, nullable, pk, default.
EXPLAIN <statement>SupportedOne row, columns explain_key, explain_value (the physical plan); executes nothing.
PREPARE / EXECUTE / DEALLOCATEPartialProtocol-level prepared statements work in every driver. SQL-level PREPARE name AS … is not supported; DEALLOCATE and DISCARD ALL are accepted with no effect.
DECLARE name CURSOR FOR <query>, FETCH [n] [FROM] name, CLOSE name | ALLPartialForward-only. FETCH BACKWARD, MOVE and SCROLL are refused. Cursors survive COMMIT (WITH HOLD behaviour).
COPY t [(cols)] FROM STDIN [WITH (FORMAT text | csv)]SupportedSame typed encoder as INSERT (types, ranges, \N for NULL, escapes; reloads pg_dump text output), flushed every 4,000 rows. A refusal mid-COPY keeps the chunks already flushed unless the COPY runs inside a transaction. CSV: quoted fields and doubled quotes, no embedded newlines. VARCHAR(n) width and TIMESTAMP(p) rounding are not yet applied on this lane.
COPY … TO …, COPY … FROM '/path'RefusedRead with SELECT; load from a file client-side via FROM STDIN.
LISTEN, NOTIFY, UNLISTENAccepted · no effect
VACUUM, ANALYZEAccepted · no effectMaintenance is `dodil data table compact
LOAD TABLE t, RELEASE TABLE t, same for GRAPH g and VECTOR INDEX t (col)SupportedPin or unpin from hot capacity; see Performance.

Multi-statement scripts over HTTP and gRPC

On the Postgres wire, several statements in one simple-query string run one after another, as in Postgres. The stateless Execute call on the gRPC and HTTP doors takes one statement per call:

  • A semicolon-separated batch is refused: Execute takes ONE statement per call — split the batch and issue the statements in order.
  • The single exception is a script that opens with BEGIN and closes with COMMIT in the same call. That is one atomic transaction: DML only, several tables allowed, a SELECT or DDL inside refused.
  • A bare BEGIN with no COMMIT in the same call is refused: there is no session to hold it. Use the Postgres wire for interactive transactions.

Details for that door are on gRPC & HTTP API → Execute; the semantics of a transaction are on Transactions & concurrency.

Data typesConstraints