Writing rows

There are two ways to write rows to a table, and they are not equivalent:

SQL DML through Execute — INSERT, UPDATE, DELETE, MERGE. Predicates, joins, subqueries, INSERT … SELECT, RETURNING. This page.
Typed RPCs — Upsert, Delete, WriteStream, Commit. No SQL text, typed RowSet payloads, no injection surface. Delete is on this page; Upsert, WriteStream and Commit are on Upsert.

There is no Insert, Merge, Update or DeleteRows RPC. dodil.tables.v1.Tables has exactly two single-shot write RPCs — Upsert and Delete — plus the WriteStream channel and the Commit transaction primitive. Everything else is SQL over Execute. Verified against dodil-tables/proto/api/tables.proto:30-125.

The dodil data table write verbs all exist; each one either renders one SQL statement and sends it through Execute, or calls a typed RPC (cli-shell/cli-k3/cmd/table.go:16-20):

CLI verb	What it actually does
`dodil data table insert`	One `INSERT INTO … VALUES (…)` per `--row`, via `Execute`.
`dodil data table upsert`	The typed `Upsert` RPC — the one write verb that does not lower to SQL.
`dodil data table merge`	One `MERGE INTO … USING (VALUES …) AS s (…) ON …`, via `Execute`.
`dodil data table update`	`UPDATE <t> SET … WHERE <predicate>`, via `Execute`.
`dodil data table delete-rows`	`DELETE FROM <t> WHERE <predicate>`, via `Execute`.

All statements return the rows_affected arm of ExecuteResponse plus statement_kind and max_wal_ulid — the session watermark to carry into your next read for read-your-writes. rows_affected counts rows the statement wrote (WAL appends plus tombstones); insert-versus-update classification is refined asynchronously on drain and is not knowable at response time.

`INSERT`

Shape	Behaviour
`INSERT INTO t (cols) VALUES (…)`	Plain insert. An existing primary key is a unique violation — SQLSTATE `23505` on the pg wire.
`INSERT INTO t VALUES (…)`	Column list filled from the catalog in table-definition order. A hidden `_rowid` PK is excluded, matching Postgres.
`INSERT INTO t VALUES (…), (…), (…)`	Multi-row. Routed as a bulk keyed write when the table has a PK.
`INSERT INTO t SELECT … FROM src`	The plane materializes the SELECT, then feeds the rows to the write path. SELECT output maps to the column list positionally, never by name.
`INSERT … ON CONFLICT DO NOTHING`	Existing rows are skipped.
`INSERT … ON CONFLICT DO UPDATE SET …`	Insert-or-update. Assignments take arbitrary expressions and literals — `SET status = 'posted'`, `SET n = n + 1`, `SET c = EXCLUDED.c` — since v0.1.27; older releases accepted only the full-row `col = EXCLUDED.col` idiom.
`INSERT … ON CONFLICT (a, b) DO …`	A conflict target naming non-PK columns must form a `UNIQUE` constraint; the write routes to that tuple’s value-keyed index.
`UPSERT INTO t …`	Normalized to `INSERT` with replace semantics. `statement_kind` comes back as `"upsert"`.
`INSERT … ON DUPLICATE KEY UPDATE …`	MySQL spelling of replace semantics.
`INSERT … RETURNING …`	Post-`DEFAULT` values of the written rows.

Refusals: INSERT with no source body.

A plain INSERT on an existing key is a 23505, by design — not an upsert. Re-running the same INSERT for an already-committed PK fails with duplicate key value violates unique constraint (SQLSTATE 23505). A handler that must be safe on re-invocation needs INSERT … ON CONFLICT (<pk>) DO UPDATE SET c = EXCLUDED.c, UPSERT INTO, or the managed dodil data table upsert.

INSERT OR REPLACE INTO parses but does nothing extra. The conflict behaviour is derived from the ON … clause only (dodil-tables/crates/tables-sql/src/lib.rs:658-666); the SQLite-style OR REPLACE modifier is never consulted, so the statement lowers as a plain INSERT and still errors on an existing key. dodil data table insert --mode overwrite emits exactly this shape. Use UPSERT INTO, ON CONFLICT DO UPDATE, or dodil data table upsert when you mean replace.

SQL


-- multi-row
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"}');
 
-- idempotent load
INSERT INTO events (id, user_id, event_type) VALUES (1, 'u-101', 'click')
ON CONFLICT DO NOTHING;
 
-- replace on key
UPSERT INTO events (id, user_id, event_type) VALUES (1, 'u-101', 'click_pricing');
 
-- derived load
INSERT INTO daily_clicks (day, user_id, n)
SELECT CAST(occurred_at AS DATE), user_id, COUNT(*)
FROM events WHERE event_type = 'click'
GROUP BY 1, 2;
 
-- get the generated key back
INSERT INTO users (email) VALUES ('[email protected]') RETURNING id, created_at;

psql


psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=acme--kb-prod user=$DODIL_KEY password=$DODIL_SECRET sslmode=require"


-- bulk load a CSV; COPY FROM STDIN is pg-wire only
\copy events (id, user_id, occurred_at, event_type) FROM 'events.csv' CSV HEADER

COPY TO and file-path COPY are explicitly refused — only COPY … FROM STDIN.

Python (psycopg)


import psycopg
 
with psycopg.connect(DSN) as conn, conn.cursor() as cur:
    cur.executemany(
        "INSERT INTO events (id, user_id, event_type) VALUES (%s, %s, %s)",
        [(1, "u-101", "click"), (2, "u-101", "click")],
    )
    conn.commit()

executemany uses prepared statements — the plane infers each placeholder’s type through InferParams, so drivers that refuse an UNKNOWN (OID 705) parameter get a concrete one.

dodil data


dodil data table insert events -b kb-prod \
  -r '{"id":1,"user_id":"u-101","event_type":"click"}' \
  -r '{"id":2,"user_id":"u-101","event_type":"click"}'


{ "rows_affected": 2 }

One INSERT statement is sent per --row (rows may differ in columns), and the printed count is their sum. Flags: -r/--row (repeatable, required) and --mode append|overwrite.

HTTP


curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/acme--kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"INSERT INTO events (id, user_id, event_type) VALUES (1, '\''u-101'\'', '\''click'\'')"}'


{ "kind": "write", "rowsAffected": 1 }

`UPDATE`

A `WHERE` clause is mandatory


UPDATE without a WHERE clause is not allowed. Use `WHERE TRUE` to update
every row, or `TRUNCATE TABLE` to clear the table while keeping the schema.

A bare UPDATE t SET … rewrites every row — the most expensive operation on the surface, and the most common cause of accidental corruption in analytic pipelines. The plane refuses it outright, matching BigQuery’s documented posture. WHERE TRUE is the explicit escape hatch. The CLI enforces the same rule locally: --predicate is required (refusing an unbounded UPDATE).

Routing

Predicate shape	Strategy	What happens
`pk = <lit>`, `pk IN (…)`, composite PK equality	Keyed	One WAL upsert per key. Non-PK conjuncts in the `WHERE` become an `extra_filter` applied after the point read.
`pk BETWEEN a AND b`, `pk >= a AND pk <= b`	Keyed range	The plane enumerates keys in the range, then upserts each. Composite-PK ranges are not supported in v1.
`pk IN (SELECT …)`	Keyed from subquery	Materialize the keys, then one WAL upsert each.
Any other predicate, on a table with a PK	Keyed from subquery (synthesised)	The plane synthesises `SELECT <pk> FROM <t> WHERE <predicate>`, materializes the matching keys, and issues one WAL upsert per key.
Any predicate, on a table with no declared PK	Warehouse update	The table has no WAL prefix at all, so a direct Delta update is the only path — and there is nothing to race with.

There is no WAL-bypass risk on a keyed table. Older docs described a non-keyed predicate as writing straight to Delta while pending WAL entries silently overwrote it on the next drain. That was a real correctness bug and it was fixed: an unrecognised predicate now synthesises a PK subquery so every write on a keyed table goes through the WAL (dodil-tables/crates/htap-planner/src/dml.rs:894-925). The trade-off is an extra read proportional to predicate selectivity.

Refusals: no WHERE clause (above); UPDATE target must be a simple table (no joins or subqueries in the target position); UPDATE SET target must be a simple column name (no composite or expression assignment targets).

SQL


-- keyed: one WAL upsert
UPDATE events SET event_type = 'click_pricing'
WHERE id = 1 AND user_id = 'u-101';
 
-- keyed range
UPDATE events SET event_type = 'archived'
WHERE id BETWEEN 1000 AND 2000;
 
-- arbitrary predicate: PKs materialized first, still WAL-routed
UPDATE events SET event_type = 'archived'
WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- explicit whole-table
UPDATE events SET reviewed = FALSE WHERE TRUE;
 
-- see what changed
UPDATE users SET tier = 'pro' WHERE id = 101 RETURNING id, tier, updated_at;

psql


psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=acme--kb-prod user=$DODIL_KEY password=$DODIL_SECRET sslmode=require" \
  -c "UPDATE events SET event_type = 'click_pricing' WHERE id = 1 AND user_id = 'u-101'"

Buffered BEGIN … COMMIT blocks work on the pg wire, including savepoints. A concurrent write that changes a rowcount your transaction already observed aborts the commit with SQLSTATE 40001 (serialization failure) — retry the transaction.

Python (psycopg)


import psycopg
 
with psycopg.connect(DSN) as conn, conn.cursor() as cur:
    cur.execute(
        "UPDATE events SET event_type = %s WHERE id = %s AND user_id = %s",
        ("click_pricing", 1, "u-101"),
    )
    print(cur.rowcount)
    conn.commit()

dodil data


dodil data table update events -b kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'" \
  --updates-json '{"event_type":"click_pricing"}'

--updates-json is a flat {column: literal} object — not wrapped in fields or any envelope. The CLI sorts the keys, renders SET "col" = <literal>, and appends your predicate verbatim. Both flags are required.

HTTP


curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/acme--kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"UPDATE events SET event_type = '\''click_pricing'\'' WHERE id = 1 AND user_id = '\''u-101'\''"}'


{ "kind": "write", "rowsAffected": 1 }

With a RETURNING clause the response takes the rows arm instead, carrying the post-write values.

`DELETE`

A `WHERE` clause is mandatory


DELETE without a WHERE clause is not allowed. Use `WHERE TRUE` to delete
every row, or `TRUNCATE TABLE` to clear the table while keeping the schema.

DELETE FROM t with no predicate was the historical footgun this rule exists to stop. WHERE TRUE is the explicit escape hatch, and TRUNCATE TABLE is the right tool for clearing a table — it keeps the schema, primary key, indexes, graph bindings and reservations. The CLI enforces the same rule locally: --predicate is required (refusing an unbounded DELETE).

Routing

Identical to UPDATE:

Predicate shape	Strategy
`pk = <lit>` / `pk IN (…)` / composite PK equality	Keyed — one WAL tombstone per key
`pk BETWEEN a AND b`	Keyed range
`pk IN (SELECT …)`	Keyed from subquery
Any other predicate, table has a PK	Keyed from a synthesised `SELECT <pk> FROM <t> WHERE <predicate>` — still fully WAL-routed
Any predicate, table has no PK	Warehouse delete (no WAL exists for such a table)

No WAL-bypass risk on a keyed table. An unrecognised predicate no longer writes straight to Delta — the plane materializes the matching keys first and tombstones each through the WAL, so a pending WAL entry can never resurrect a deleted row on the next drain.

SQL


-- keyed
DELETE FROM events WHERE id = 1 AND user_id = 'u-101';
 
-- arbitrary predicate: PKs materialized first, still WAL-routed
DELETE FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- explicit whole-table (prefer TRUNCATE)
DELETE FROM events WHERE TRUE;
 
-- see what went
DELETE FROM events WHERE id = 1 RETURNING id, user_id, event_type;

psql


psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=acme--kb-prod user=$DODIL_KEY password=$DODIL_SECRET sslmode=require" \
  -c "DELETE FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00'"

dodil data


dodil data table delete-rows events -b kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'"

--predicate is the only flag and it is required.

HTTP


curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/acme--kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"DELETE FROM events WHERE id = 1 AND user_id = '\''u-101'\''"}'


{ "kind": "write", "rowsAffected": 1 }

ExecuteResponse.rows_affected with statement_kind: "delete" — the tombstone count for a keyed delete, or the Delta row count for a keyless table. With RETURNING, the response takes the rows arm carrying the deleted rows.

`MERGE`

MERGE follows BigQuery clause semantics: the WHEN clauses are evaluated in statement order and the first true one wins per row. The ON condition is split into same-name equijoin pairs (which become the routing match columns) and a non-equijoin residue applied afterwards.

Clause	Legal actions
`WHEN MATCHED [AND <pred>]`	`UPDATE SET …` · `DELETE`
`WHEN NOT MATCHED [BY TARGET] [AND <pred>]`	`INSERT (cols) VALUES (…)` · `INSERT ROW`
`WHEN NOT MATCHED BY SOURCE [AND <pred>]`	`UPDATE SET …` · `DELETE`

Three source shapes are accepted after USING:

Source	Notes
A bare table name	The plane runs `SELECT * FROM <name>` first.
`(VALUES (…), (…)) AS s (col, …)`	Rows extracted at plan time.
A `SELECT` subquery	Materialized first; its primary `FROM` table is used as the view hint.

Writes route by the target’s declared primary key; when the target has none, the ON match columns become the routing keys.

Refusals: INSERT outside WHEN NOT MATCHED [BY TARGET] (and conversely only INSERT is legal there); MERGE INSERT VALUES with more than one row; a column list whose arity differs from its VALUES; a SET target that is not a plain column; non-literal cells in USING (VALUES …) ("column '<c>' must be a literal in v1 (no expressions / function calls)" — use a SELECT source instead); an AS-clause column list whose length does not match the VALUES width; and any other USING shape ("only bare table, VALUES, or SELECT subquery supported in v1").

SQL


-- upsert from a staging table
MERGE INTO users AS t
USING staging_users AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET email = s.email, tier = s.tier
WHEN NOT MATCHED THEN INSERT (id, email, tier) VALUES (s.id, s.email, s.tier);
 
-- literal source, composite key
MERGE INTO events AS t
USING (VALUES (1, 'u-101', 'click_pricing'), (4, 'u-103', 'signup'))
      AS s (id, user_id, event_type)
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET event_type = s.event_type
WHEN NOT MATCHED THEN INSERT (id, user_id, event_type)
     VALUES (s.id, s.user_id, s.event_type);
 
-- full sync: reconcile both directions
MERGE INTO users AS t
USING (SELECT * FROM staging_users) AS s
ON t.id = s.id
WHEN MATCHED AND s.deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET email = s.email
WHEN NOT MATCHED THEN INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN DELETE;

INSERT ROW inserts the whole source row — the column list is inferred.

psql


psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=acme--kb-prod user=$DODIL_KEY password=$DODIL_SECRET sslmode=require" \
  -f merge_users.sql

dodil data


dodil data table merge events -b kb-prod \
  --match-column id --match-column user_id \
  --when-matched update --when-not-matched insert \
  -r '{"id":1,"user_id":"u-101","event_type":"click_pricing"}' \
  -r '{"id":4,"user_id":"u-103","event_type":"signup"}'

--match-column is required. --when-matched accepts update (default), delete or skip; --when-not-matched accepts insert (default) or skip/ignore. The CLI takes the union of columns across all --row flags, fills missing cells with NULL, and renders one statement.

HTTP


curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/acme--kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"MERGE INTO users AS t USING staging_users AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET email = s.email WHEN NOT MATCHED THEN INSERT ROW"}'


{ "kind": "write", "rowsAffected": 240 }

ExecuteResponse.rows_affected, with statement_kind: "merge" — WAL appends plus tombstones, not an insert/update/delete breakdown. That classification is refined asynchronously when the compactor drains and is not knowable at response time.

`Delete` (typed RPC)

The typed sibling of Upsert — a key-only delete. keys carries just the primary-key columns; there is no predicate. It is one of the two single-shot write RPCs on dodil.tables.v1.Tables.

gRPC


rpc Delete(DeleteRequest) returns (WriteAck);
 
// Key-only delete: `keys` carries just the PK columns.
message DeleteRequest {
  string db_id = 1;
  string table = 2;
  RowSet keys = 3;
  // The primary-key columns. EMPTY = resolve from the catalog's declared
  // PRIMARY KEY; FAILED_PRECONDITION when the table declares none.
  repeated string match_columns = 4;
}


{
  "db_id": "acme--kb-prod",
  "table": "events",
  "keys": {
    "typed": {
      "columns": [
        { "name": "id",      "type": { "code": "TYPE_CODE_INT64" } },
        { "name": "user_id", "type": { "code": "TYPE_CODE_STRING" } }
      ],
      "rows": [
        { "values": [{ "int64_value": "1" }, { "string_value": "u-101" }] }
      ]
    }
  }
}

Returns a WriteAck — wal_written plus the wal_ulid watermark. Same contract as Upsert: no row count, no version, no pending_drain.

Delete batches also ride the WriteStream channel and the Commit primitive, interleaved with upserts in order — see Upsert → WriteStream.

Writing rows

`INSERT`

SQL

psql

Python (psycopg)

dodil data

HTTP

`UPDATE`

A `WHERE` clause is mandatory

Routing

SQL

psql

Python (psycopg)

dodil data

HTTP

`DELETE`

A `WHERE` clause is mandatory

Routing

SQL

psql

dodil data

HTTP

`MERGE`

SQL

psql

dodil data

HTTP

`Delete` (typed RPC)

gRPC

SQL equivalent

See also

Writing rows

INSERT

SQL

psql

Python (psycopg)

dodil data

HTTP

UPDATE

A WHERE clause is mandatory

Routing

SQL

psql

Python (psycopg)

dodil data

HTTP

DELETE

A WHERE clause is mandatory

Routing

SQL

psql

dodil data

HTTP

MERGE

SQL

psql

dodil data

HTTP

Delete (typed RPC)

gRPC

SQL equivalent

See also

`INSERT`

`UPDATE`

A `WHERE` clause is mandatory

`DELETE`

A `WHERE` clause is mandatory

`MERGE`

`Delete` (typed RPC)