INSERT

INSERT shapes through Tables.Execute. The planner classifies each statement by whether the target declares merge_keys and by the source — a single VALUES row, multiple VALUES rows, or a SELECT. For the RPC contract, see the Execute hub.

The response carries a row count and nothing else. ExecuteResponse for a write is rows_affected (a uint64) plus statement_kind, max_wal_ulid and warnings. There is no strategy, pending_drain, noop or target_table field — those never existed on the wire. Over the tables HTTP door: {"kind":"write","rowsAffected":3}.

Keyed inserts

A table with merge_keys routes every insert through the WAL; the compactor MERGEs it into Delta on the next drain. Safe under concurrent writes, and visible to your own next read via the min_ulid watermark.

A keyed INSERT is not an upsert. Inserting a primary key that already exists fails with duplicate key value violates unique constraint — SQLSTATE 23505, by design. For an idempotent write use INSERT … ON CONFLICT (<pk>) DO UPDATE SET c = EXCLUDED.c, UPSERT INTO, or the managed dodil data table upsert.

Single row — `KeyedInsertSingle`


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"}');

Multi-row `VALUES` — `KeyedInsertBulk`


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}');

rows_affected is 3.

`INSERT … SELECT` — `KeyedInsertFromSelect`


INSERT INTO click_summary (user_id, n)
SELECT user_id, COUNT(*) FROM events
WHERE event_type = 'click'
GROUP BY user_id;

The dispatcher materialises the source rows, then bulk-appends them to the WAL.

The column list maps positionally, never by name. INSERT INTO t (a, b) SELECT x, y … binds x→a and y→b in that order (crates/htap-planner/src/ir.rs:129-133). Omit the list and the target’s declared column order applies.

Non-keyed inserts (Delta-direct)

A table with no declared pk_columns has no WAL prefix, so inserts append straight to Delta — NonKeyedInsert and NonKeyedInsertFromSelect.


-- audit_log declares no primary key
INSERT INTO audit_log (ts, actor, action, target) VALUES
  (CURRENT_TIMESTAMP, 'admin', 'optimize', 'events'),
  (CURRENT_TIMESTAMP, 'admin', 'vacuum',   'events');
 
INSERT INTO event_archive
SELECT * FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';

This is BigQuery’s posture — INSERT works on any table regardless of key declarations. Nothing here races the compactor, because there is no WAL for such a table to race against.

`UPSERT INTO`

UPSERT INTO is normalised into a keyed merge before planning, so it is the terse form of “insert or replace by primary key”:


UPSERT INTO users (id, email, tier) VALUES ('u-101', '[email protected]', 'pro');

For explicit matched/not-matched actions, use MERGE.

Bulk loading

For volume, the Postgres wire is the fast path — it is the only path offering COPY … FROM STDIN:


psql "postgresql://$USER:$TOKEN@pg.uk-lon-1.dodil.io:5432/kb-prod?sslmode=require" \
  -c "\copy events (id, user_id, occurred_at, event_type, payload) FROM 'events.csv' WITH (FORMAT csv, HEADER true)"

COPY TO and file-path COPY are explicitly refused (crates/adapter/src/pg/copy.rs).

INSERT

Keyed inserts

Single row — KeyedInsertSingle

Multi-row VALUES — KeyedInsertBulk

INSERT … SELECT — KeyedInsertFromSelect