SQL Compatibility

K3 Tables speak DuckDB-flavored SQL. Anything DuckDB parses — joins, CTEs, window functions, JSON operators, set ops, subqueries — K3 can plan and dispatch, plus a set of K3 extensions (pgvector KNN operators, graph DDL, residency verbs, RESTORE, IMPORT/EXPORT TABLE). There is an honest refusals list, and the planner picks a strategy per statement based on the table’s primary key, partitioning, and the query shape.

This page is the SQL contract: what dialect, what types, what statement shapes are supported, what’s not, and how to connect a native client.

Dialect

Why DuckDB: it’s a single, well-documented, embeddable SQL parser + planner with strong analytical-workload focus. K3 hands DuckDB the SQL, gets a parsed plan back, then routes the operation across the write log + Delta tiers via its own dispatcher. You write standard DuckDB SQL; K3 worries about the tier routing.

Where K3 diverges from stock DuckDB

These are additions and rewrites applied before or around the DuckDB parse — none of them change the meaning of a query stock DuckDB would already accept:

Column types

Fifteen column types in the plane’s type vocabulary — all serialize losslessly to Delta. The pg OID column is what a Postgres-wire client sees in the RowDescription:

INTEGER is stored as 64-bit. A SQL INTEGER/INT column resolves to the long vocab type, not int. Auto-generated surrogate keys are 64-bit snowflake ids, so a foreign key declared integer to match a PK’s declared type would silently truncate to its low 32 bits otherwise. Introspection therefore reports BIGINT for a column you declared INTEGER — a parity nit traded against silent data loss. The int vocab term still exists and is accepted by the structured CreateTable RPC.

long values that exceed safe-integer range (±2^53 − 1) — pass as a JSON string to preserve precision. K3 stores int64 natively, and a BIGINT past 2^53 survives every entry and read path exactly.

The advertised widths are true, not merely stable. Since tables v0.1.38 a SMALLINT advertises int2, an INT32 advertises int4, a REAL advertises float4, and UUID is a type of its own (OID 2950, the 16 raw bytes in binary format, a non-uuid refused 22P02 rather than stored). Earlier releases advertised int8/float8/text for all of them — stably wrong, which is exactly what a stability check passes.

VECTOR needs a dimension. Bare VECTOR is rejected at DDL naming the column; pgvector’s dimension-less form (typed on first insert) has no equivalent here. NUMERIC/DECIMAL default to decimal(38,9); precision above 38 (Arrow Decimal128’s max) is rejected rather than narrowed.

array / vector columns are advertised as text on the pg wire rather than a native pg array OID — both text and binary format are then trivially valid, so a binary-binding driver never breaks on them. Native array binary encoding is a follow-up.

Type fidelity — exact, or refused

The standing rule the engine enforces, and the reason these tables are safe to put money in:

A value the declared column can hold round-trips exactly, by every entry path and every read path. Where it cannot, the write is REFUSED loudly, naming the column and the constraint. Silently storing — or serving — a different number is never a legal outcome.

That is a tested contract, not a design intention. Every declared type is measured across four entry paths — SQL literal, text-format bind, binary-format bind, COPY … FROM STDIN — times two read formats (text and binary), and the advertised OID is asserted true rather than merely stable. The same matrix runs across the other wires the plane serves, so the value you recover on Postgres equals the value you recover over gRPC or Bolt after canonicalisation. It is re-run every release.

Crypto-grade numeric precision

DECIMAL(38,18) holds an 18-decimal token amount exactly. DECIMAL(38,0) holds raw wei — all 27 integer digits of it. Not rounded, not approximated, not “close enough for display”: the same digits you wrote come back, on every entry path and every read path, verified per release.

There is no float detour anywhere on that path. Decimals are Decimal128 end to end, carried on the wire as canonical decimal text, and parsed by one shared routine that the SQL literal, the text bind, the binary bind and COPY all call — so a value cannot change depending on which door it came through. A cent cannot move at 2^53, because nothing on the path is an f64.

CREATE TABLE positions (
  id       BIGINT PRIMARY KEY,
  wei      DECIMAL(38,0),    -- raw wei: 27 integer digits, exact
  tokens   DECIMAL(38,18),   -- 18-decimal token amounts, exact
  usd      DECIMAL(18,2)     -- ordinary money
);

Declare the scale you actually need and the engine keeps it. Never store money as DOUBLE, as TEXT, or as integer-cents-out-of-caution — none of those is safer here, and two of them are worse.

The rules around the edges

• 38 digits is a platform cap, not a tunable. Arrow’s Decimal128, the Delta Lake protocol and DuckDB each cap decimal precision at 38 independently, and Delta’s is the storage format. NUMERIC(39) is rejected at DDL, never silently narrowed. Postgres’s unconstrained NUMERIC (131 072 integer digits) has no equivalent here.
• Bare NUMERIC is decimal(38,9), and it says so. CREATE TABLE emits a NOTICE naming the column, the type it actually got, what happens to extra digits, and the fix. Because the engine chose that scale, such a column refuses a value it would have to round — Postgres would not have lost those digits. A scale you declared rounds the way pg does: 1.005 into NUMERIC(10,2) stores 1.01.
• Exponent forms are exact numerics. 1e-6 into a column that can hold 0.000001 stores 0.000001, on every entry path (Postgres §4.1.2.6 makes a constant carrying an exponent an exact numeric).
• Overflow is 22003; malformed input is 22P02. A SMALLINT out of range refuses rather than wrapping, a REAL overflow refuses rather than becoming inf, and an impossible date or a non-uuid is refused rather than landing NULL.
• JSON is served verbatim on this wire — key order, duplicate keys and integers past 2^53 all survive.
• Two places digits do move, both deliberate and both avoidable. A decimal nested inside an array<T> or struct<…> is demoted to f64 — give money its own column. And in-engine division truncates where Postgres rounds (CAST(2.01 / 2 AS DECIMAL(10,2)) → 1.00, pg gives 1.01; an upstream DuckDB issue, tracked): wrap the division in an explicit ROUND(…, s) when the last digit matters.
• VARCHAR(n) / CHAR(n) lengths are parsed and not enforced — VARCHAR(5) accepts a megabyte and CHAR(10) does not blank-pad. Use a CHECK constraint, which is enforced.
• One timestamp vocabulary, timestamptz. Offsets you send are converted, not sliced off, and stored and served as UTC microseconds. An offset-less literal is read as UTC where pg would use the session TimeZone; there is no TIMESTAMP WITHOUT TIME ZONE.

default_expression

Every column may carry a default expression — literal SQL applied at row-commit time when the column is absent from the input row. Validated at table creation:

The trailing () is optional (CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are both accepted). Any other expression is rejected at table creation, and so is a function whose output type doesn’t match the declared column type — you find out at DDL, never on the first insert. A default fires only when the column is absent from the input row; an explicit null from the caller is honored as-is.

Default expressions and SQL functions are different contexts. The table above is the default_expression vocabulary, validated at DDL. In query SQL, UUIDs come from the ordinary functions — gen_random_uuid(), UUID(), and casts (UUID()::text) all work in a SELECT or DML expression. GENERATE_UUID() is not a SQL function — it is only valid as a column default_expression.

Statement shapes — supported

What you can put in an Execute SQL string:

DDL

-- Create a table with a composite primary key + partition column + JSON column
CREATE TABLE events (
  id         BIGINT NOT NULL,
  user_id    VARCHAR NOT NULL,
  occurred_at TIMESTAMP NOT NULL,
  event_type VARCHAR NOT NULL,
  payload    JSON,
  PRIMARY KEY (id, user_id)
) PARTITIONED BY (event_type);
 
-- Add / drop / widen columns (multiple operations in one ALTER are fine)
ALTER TABLE events ADD COLUMN session_id VARCHAR;
ALTER TABLE events DROP COLUMN session_id;
ALTER TABLE events ALTER COLUMN id SET DATA TYPE BIGINT;
 
-- Drop a table
DROP TABLE events;
DROP TABLE IF EXISTS events;
 
-- Clear every row, keep the schema, PK, indexes, graph bindings, reservations
TRUNCATE TABLE events;
TRUNCATE events;
 
-- CTAS (CREATE TABLE AS SELECT) — schema inferred from the SELECT
CREATE TABLE click_summary AS
  SELECT user_id, COUNT(*) AS n
  FROM events
  WHERE event_type = 'click'
  GROUP BY user_id;
 
-- Indexes. An index IS a table (`__idx_{table}_{column}` / `__uidx_…`).
CREATE INDEX ON events (user_id);            -- non-unique: exactly one column
CREATE UNIQUE INDEX ON events (id, user_id); -- unique: composite allowed
CREATE INDEX ON docs USING hnsw (emb vector_cosine_ops)
  WITH (m = 16, ef_construction = 64, async);
 
-- Time travel, in reverse
RESTORE TABLE events TO VERSION AS OF 42;
RESTORE TABLE events TO TIMESTAMP AS OF '2026-05-27T10:00:00Z';
 
-- Catalog statements
CREATE DATABASE [IF NOT EXISTS] analytics;
SHOW DATABASES;   -- SHOW SCHEMAS is a synonym
SHOW TABLES;
DESCRIBE events;  -- DESC events
DESCRIBE DATABASE analytics;
 
-- Plan inspection — renders the typed plan as one row, executes nothing
EXPLAIN SELECT * FROM events WHERE id = 1;

Notes:

• PRIMARY KEY is honored — inline column-level (id BIGINT PRIMARY KEY) and table-level (PRIMARY KEY (id, user_id)) both work. The key flows to the planner so subsequent writes get keyed routing for free.
• PARTITIONED BY (col, …) controls Delta partitioning. Reads that filter on a partition column can prune entire partitions.
• CTAS infers types from the SELECT — int-shaped JSON numbers → long, fractional → double, strings → string, bools → boolean. For typed migrations, pass explicit columns instead via the structured CreateTable RPC.
• DROP COLUMN and ALTER COLUMN … SET DATA TYPE are logical — recorded on the sidecar, subtracted from (or cast on) every schema surface at read time; no file rewrite. Retypes are widening-only and validated at the executor.
• Vector-index operator classes are vector_l2_ops (the default) / vector_ip_ops / vector_cosine_ops. WITH accepts m, ef_construction, lists, quantization (hnsw: f16 | i8; ivfflat: sq8), and the K3 extension async (register building, build in the background — watch SHOW VECTOR INDEXES). An unknown WITH key is an error, never a silently-kept default.
• RESTORE … TO TIMESTAMP AS OF requires an offset (…Z, …+02:00). It is destructive to the tail by design: writes acked after the target version are discarded and the un-drained write log is wiped so they can’t resurrect.

Reserved words as column names — quote or rename. DuckDB’s reserved words apply, and some bite common column names: at is reserved (a natural audit-timestamp name), and so are end, order, table, to, user. A bare at TIMESTAMP in a CREATE TABLE (or WHERE at > …) is a parse error. Double-quote the identifier ("at" TIMESTAMP, WHERE "at" > …) — quoting must then be used everywhere the column is referenced — or pick an unreserved name like occurred_at.

Constraints: what is enforced

Which constraint forms the engine actually enforces is load-bearing information for anyone modelling a transactional schema, so here is the complete matrix. The rule since tables v0.1.31: a constraint is enforced, rejected at DDL, or accepted with a warning — never silently ignored.

What FOREIGN KEY covers, and what you still own. An INSERT or UPSERT naming a missing parent is refused with 23503. The parent probe is not a blocking lock — this plane has none — it is a guard-only key folded into the commit: a concurrent write to that parent (including the DELETE that would orphan your child) lands a newer version than the probe observed, so the whole commit aborts with 40001 rather than letting an orphan through. Two children of the same parent take no claim on each other and both commit, so a popular parent is not a serialization point.

What is not checked is the other direction: deleting a parent that still has children succeeds, and so does an autocommit UPDATE of an FK column. Keep those in the writer — the parent-delete check is the one to write yourself, and SELECT … FOR UPDATE on the parent makes it conflict-safe.

Enforcement is not retroactive, and old migrations may now fail. An FK declared while enforcement was off was discarded rather than stored, and it does not reappear when enforcement is on — re-declare it with ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY, which is what moves enforcement onto an existing table. And a migration carrying ON DELETE CASCADE or DEFERRABLE now fails at DDL where it used to pass silently. That is the point: a clause that was never going to run no longer claims it will.

Residency verbs

LOAD TABLE / RELEASE TABLE set and clear the catalog pin readers warm and mirror from — this is the pinning verb the capacity model refers to, and it is separate from a reservation (which is a budget, never a pin list). The same shape exists for graphs and vector indexes:

LOAD TABLE orders;
RELEASE TABLE orders;
LOAD GRAPH social;
RELEASE GRAPH social;
LOAD VECTOR INDEX docs (emb);
RELEASE VECTOR INDEX docs;
SHOW VECTOR INDEXES;

The identifier may be double-quoted (case preserved) and a single trailing ; is allowed; exactly one identifier follows the verb.

DML — writes

-- Single-row insert (keyed)
INSERT INTO events (id, user_id, occurred_at, event_type, payload)
  VALUES (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click', '{"page":"/pricing"}');
 
-- Bulk insert (keyed)
INSERT INTO events (id, user_id, occurred_at, event_type, payload) VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click',    '{"page":"/pricing"}'),
  (2, 'u-101', TIMESTAMP '2026-05-27 10:01:00', 'click',    '{"page":"/signup"}'),
  (3, 'u-102', TIMESTAMP '2026-05-27 10:02:00', 'purchase', '{"sku":"A-12","amount":49.99}');
 
-- INSERT … SELECT (keyed-from-select). Columns map BY POSITION, not by name.
INSERT INTO click_summary
  SELECT user_id, COUNT(*) FROM events WHERE event_type = 'click' GROUP BY user_id;
 
-- Conflict handling on the PK
INSERT INTO events (id, user_id) VALUES (1, 'u-101') ON CONFLICT DO NOTHING;
INSERT INTO events (id, user_id) VALUES (1, 'u-101')
  ON CONFLICT (id, user_id) DO UPDATE SET user_id = EXCLUDED.user_id;
INSERT INTO events (id, user_id, event_type) VALUES (1, 'u-101', 'click')
  ON CONFLICT (id, user_id) DO UPDATE SET event_type = 'reclick';  -- expressions & literals OK
INSERT INTO events (id, user_id) VALUES (1, 'u-101')
  ON DUPLICATE KEY UPDATE user_id = VALUES(user_id);   -- MySQL spelling, same lowering
UPSERT INTO events (id, user_id) VALUES (1, 'u-101');  -- K3 spelling of insert-or-replace
 
-- RETURNING on any DML
INSERT INTO events (user_id) VALUES ('u-101') RETURNING id, occurred_at;
UPDATE events SET event_type = 'x' WHERE id = 1 RETURNING *;
 
-- UPDATE via PK (keyed → write log)
UPDATE events SET event_type = 'click_pricing'
  WHERE id = 1 AND user_id = 'u-101';
 
-- UPDATE via non-PK predicate (non-keyed → ⚠️ Delta-only, see warnings)
UPDATE events SET event_type = 'archived' WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- DELETE via PK (keyed)
DELETE FROM events WHERE id = 1 AND user_id = 'u-101';
 
-- DELETE via non-PK predicate (non-keyed → ⚠️)
DELETE FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- MERGE (upsert) — source is inline rows / a query / another table
MERGE INTO events AS t
USING (SELECT * FROM events_staging) AS s
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT (id, user_id, occurred_at, event_type, payload)
  VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

Routing rule of thumb: predicates / ON clauses that match the table’s merge_keys route through the write log (the planner picks a KEYED_* strategy). Predicates that don’t route directly to Delta — fast but bypass the log, which carries the WAL-overwrite risk discussed in Execute → UPDATE.

UPDATE and DELETE require a WHERE clause. A bare UPDATE t SET … or DELETE FROM t is refused — this is the foot-gun the rule exists to stop. Spell the intent: WHERE TRUE to touch every row, or TRUNCATE TABLE t to clear the table while keeping its schema.

ON CONFLICT notes: with no conflict target the PK is the target; a named target must be the PK or a UNIQUE index (checked at execution). DO UPDATE SET assignments take arbitrary expressions and literals — SET status = 'posted', SET n = n + 1, SET c = EXCLUDED.c — since v0.1.27 (earlier releases accepted only the full-row col = EXCLUDED.col idiom).

A plain INSERT on an existing key is a 23505, by design. An INSERT without a conflict clause is never an upsert — re-inserting an already-committed PK fails with duplicate key value violates unique constraint (SQLSTATE 23505). Handlers that must be safe on re-invocation should use ON CONFLICT (<pk>) DO UPDATE, UPSERT INTO, or the managed dodil data table upsert.

DML — reads

-- Simple SELECT
SELECT * FROM events WHERE event_type = 'click' LIMIT 100;
 
-- Aggregates (federated across partitions when applicable)
SELECT user_id, COUNT(*) AS n
FROM events
GROUP BY user_id
ORDER BY n DESC;
 
-- Joins
SELECT e.user_id, u.email, COUNT(*) AS clicks
FROM events e JOIN users u ON e.user_id = u.id
WHERE e.event_type = 'click'
GROUP BY e.user_id, u.email;
 
-- Window functions
SELECT user_id, occurred_at,
       LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_event
FROM events;
 
-- CTEs (WITH …)
WITH purchases AS (
  SELECT user_id, payload->>'sku' AS sku, (payload->>'amount')::double AS amount
  FROM events WHERE event_type = 'purchase'
)
SELECT sku, SUM(amount) AS total FROM purchases GROUP BY sku;
 
-- Subqueries
SELECT * FROM events WHERE user_id IN (SELECT id FROM users WHERE tier = 'pro');
 
-- Filter by a list of ids with one bound array parameter (pg wire) — no
-- string-built IN (…) needed
SELECT * FROM events WHERE id = ANY($1);
--   e.g. psycopg: cur.execute("SELECT * FROM events WHERE id = ANY(%s)", ([1, 2, 3],))
--   SQLAlchemy:   text("SELECT * FROM events WHERE id = ANY(:ids)"), {"ids": [1, 2, 3]}
 
-- Set ops
(SELECT user_id FROM events WHERE event_type = 'click')
INTERSECT
(SELECT user_id FROM events WHERE event_type = 'purchase');

All standard DuckDB read shapes work. The planner picks UNARY_WAREHOUSE, UNARY_MERGED_STRONG, or FEDERATED_* depending on the write-log backlog, partitioning, and aggregate-vs-scan shape — see Read freshness and Execute → SELECT.

Read freshness

Freshness is no longer a client knob. ExecuteRequest.freshness (and the CLI’s --freshness) still exist on the wire for compatibility, but the runtime ignores the requested value: reads are read-your-writes by default, and state decides which physical strategy serves them.

The coordinator runs a frontier check before every read — one in-memory call asking the writer whether the database’s write-log backlog is empty on an authoritative shard. If it is, every acked write is already durable in Delta, so eventual ≡ strong and the read takes the analytical fast path (Delta snapshot, distributed fan-out, spool, result cache). If emptiness cannot be proven — writer unreachable, shard absent, mid-failover — the read falls back to the merged write-log + Delta path. The check is deliberately conservative: a false negative costs one strong read; a false positive would cost truth.

You can still see which path served a read — the response’s QueryStrategy / ServedBy fields report it. See Execute → SELECT.

JSON columns

K3’s json column is a first-class semantic type. Stored as canonical JSON text under the hood, tagged so DuckDB recognizes it. Every DuckDB JSON operator works:

-- Field access
SELECT payload->'sku' FROM events;            -- returns JSON
SELECT payload->>'sku' FROM events;           -- returns TEXT
 
-- JSON path
SELECT json_extract(payload, '$.amount') FROM events;
SELECT json_extract_string(payload, '$.sku') FROM events;
 
-- Array / object shape
SELECT json_array_length(payload->'tags') FROM events;
SELECT json_keys(payload) FROM events;
SELECT unnest(json_extract(payload, '$.tags[*]')) FROM events;
 
-- Cast extracted scalars
SELECT (payload->>'amount')::double AS amount FROM events;
 
-- Round-trip a Postgres-style cast
SELECT payload::json->'tags'->>0 FROM events;

For high-volume read paths, promote hot fields to typed columns; reserve json for the long tail / unstructured metadata. Note that DuckDB has no json_each — that’s the SQLite/Postgres spelling; use json_keys or unnest(json_extract(…)).

Nested array<T> and struct<…> columns store as native Delta Array/Struct, so col, col->'k', col->>'k' and json_array_length(col) all project. On the pg wire a struct reads as jsonb and an array as a {…} array literal.

Vector KNN — pgvector operators

The pgvector distance operators work over vector(N) columns — K3 rewrites them onto the plane’s distance functions at parse time:

-- L2 distance
SELECT id FROM docs ORDER BY emb <-> '[0.12, -0.04, ...]' LIMIT 10;
 
-- Negative inner product
SELECT a <#> b FROM t;
 
-- Cosine distance (with an optional pgvector-style cast)
SELECT id FROM docs ORDER BY emb <=> '[1,2,3]'::vector LIMIT 5;

Exactly these three are wired. The rewrite runs before the DuckDB parse (which rejects <-> and <#> outright) and wraps both operands in CAST(… AS FLOAT[]), so a text literal, a FLOAT[n] overlay column and a FLOAT[] Delta column all unify without knowing the dimension. An explicit '[…]'::vector cast is stripped first — DuckDB has no vector type — so the literal casts to FLOAT[] directly. An operator that appears inside a string literal is left alone.

ANN behaviour is tuned with session GUCs on the pg wire: SET hnsw.ef_search = N, SET ivfflat.probes = N, SET dodil.vector_exact_scan = on, SET dodil.vector_consistency = strong|eventual.

Graph DDL

Graphs are Tables-pillar objects defined over tables in the same bucket — see Graph. The grammar:

CREATE GRAPH [IF NOT EXISTS] <name>
    NODES ( <table> [KEY <col>] [, …] )
    EDGES ( <table> SRC <col> DST <col> [, …] )
DROP GRAPH [IF EXISTS] <name>
SHOW GRAPHS

CREATE GRAPH IF NOT EXISTS social
  NODES (people KEY id)
  EDGES (follows SRC src DST dst);
DROP GRAPH IF EXISTS social;
SHOW GRAPHS;

KEY is optional and defaults to id. SRC and DST are mandatory — there is no sane default for an edge’s direction, so EDGES (follows) is a parse error. Identifiers may be double-quoted; the node/edge lists must not be empty. DROP GRAPH removes the catalog entry and its artifacts — the member node/edge tables are ordinary tables and are untouched.

Multi-statement batches and transactions

Semicolon-separated statements run as a sequential batch, not a transaction: in-order execution, stop-at-first-error, everything before the failure stays applied. The response carries statement_results[] (one per applied statement), error, failed_statement_index, and failed_sql. See Execute → Multi-statement batches.

A script that opens with BEGIN / START TRANSACTION and closes with COMMIT in the same call is handled differently — it is a transaction block, and it lowers to one atomic commit:

BEGIN;
  INSERT INTO events (id, user_id) VALUES (1, 'u-101');
  DELETE FROM events WHERE id = 2;
COMMIT;

Rules for a transaction block:

• DML only — INSERT / UPSERT / UPDATE / DELETE. A SELECT, a DDL statement, or a nested BEGIN inside the block is refused; none has atomic meaning on the write log. MERGE parses into a block but is refused at lowering — issue it as its own statement, where it is already atomic per target table.
• One table — v1 lowers the block to a single atomic commit only when every statement targets the same (database, table). A cross-table block is built and routed but the writer rejects it UNIMPLEMENTED, so nothing durable moves.
• Every target needs a declared PRIMARY KEY — every write in this plane is keyed.
• Statements later in the block read your earlier ones (an intra-block overlay over committed ∪ overlay), within a table.
• ROLLBACK anywhere discards the whole block — nothing is written.
• Statements after COMMIT belong to a separate transaction and are not run.
• A bare interactive BEGIN (no COMMIT/ROLLBACK in the same call) is refused over Execute: the plane is stateless. Use the Postgres wire, which buffers a real session transaction — including SAVEPOINT, RELEASE, and ROLLBACK TO SAVEPOINT — and flushes it as one atomic block at COMMIT. Inside such an open pg-wire transaction a SELECT does see the block’s own writes (v0.1.39+) — see Transactions below. The SELECT-inside-a-block refusal above is about the stateless Execute script, which has no session to read through.

Refusals

The honest unsupported list. Each of these is valid SQL somewhere, and each fails loudly rather than silently doing something else.

Statements and shapes

Write-shape refusals

Partially enforced, or accepted and ignored

FOREIGN KEY is enforced on the INSERT side since v0.1.36 — what is not checked is the DELETE side and an autocommit UPDATE of an FK column. See Constraints: what is enforced for the full matrix (CHECK is enforced since v0.1.25; unenforceable forms are rejected at DDL since v0.1.31; unenforceable FK clauses since v0.1.36). FOR UPDATE takes no blocking lock; inside a transaction the locked rows’ keys are commit-guarded (a conflicting write aborts the transaction with 40001 — real write-skew protection), outside one it is a snapshot read with a NOTICE. SET/RESET of GUCs other than statement_timeout and the vector knobs are accepted and ignored, as are DEALLOCATE, DISCARD, LISTEN, UNLISTEN, and CREATE SCHEMA public.

Multiple ADD COLUMNs in a single ALTER TABLE are supported, and so is mixing ADD / DROP / ALTER COLUMN in one statement.

Native clients

Three ways to send SQL to K3 — all speak the same dialect and reach the same planner:

Postgres wire

The richest surface: psql, psycopg, pgx, SQLx, JDBC, and ORMs (SQLAlchemy, Django, Prisma, GORM) connect to the pg adapter with the bucket as the database. See Connect & Adapters for the endpoint and credentials.

Beyond the dialect above, the pg wire adds session state the stateless Execute RPC has no place for:

HTTP — Execute RPC

curl -sS -X POST "https://api.data.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"
  }'

Response is a oneof — query / write / ddl — depending on the SQL kind. See API Reference → Execute.

CLI — dodil data table query

dodil data table query --bucket kb-prod "
  SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type
"
 
# JSON output for scripting
dodil data table query --bucket kb-prod "SELECT * FROM events LIMIT 5" -o json

The CLI’s table query command handles both reads and writes — it picks the right RPC based on what the planner returns. Its --freshness flag is retained but inert: it prints a note and is ignored (see Read freshness).

Structured shortcuts (no SQL string)

If you’d rather not assemble SQL strings, the typed RPCs (Insert / Merge / Update / DeleteRows / Query) and their CLI commands (dodil data table insert / merge / update / delete-rows / query) accept JSON rows + predicates / match-columns. The planner routes them through the same strategies — they’re just shortcuts for callers that prefer typed inputs to SQL templating. See API Reference → Data.

Errors and retries

The SQLSTATE class is the contract clients build retry logic on — key on it, not on message text:

A retry loop should match the transient classes (08006, 58030, XX000, and 40001 for transactions) and give up immediately on everything in the 42… family. Since v0.1.27 transient object-store / IO faults are correctly classed as retryable — earlier releases could misreport them as 42601, so if you built a workaround that retries syntax errors, remove it.

Coming-from-X cheatsheet

See also

• API Reference → Execute — every SQL shape → strategy + runnable examples
• Core Concepts — QueryStrategy, WriteStrategy, Freshness, Column types
• Quickstart — end-to-end in 5 minutes
• DuckDB SQL reference  — the upstream dialect docs