DML — Data Manipulation Language

Goal

Reference for supported DML operations and mutation policy behavior.

INSERT

INSERT INTO t (id, v) VALUES (1, 100);
INSERT INTO t (id, v) VALUES (2, 200), (3, 300);

For partitioned tables, an INSERT into the parent table automatically routes the row to the appropriate partition. If no suitable partition (and no DEFAULT) is found — 23514 check_violation.

INSERT … SELECT

RM-0.6.3.7 (RFC-2026-497, S8): the source of an INSERT can be an arbitrary SELECT query. The result columns are mapped to the target columns positionally (or by explicit column list); SERIAL/IDENTITY/DEFAULT are resolved independently for each row.

INSERT INTO archive (id, v) SELECT id, v FROM t WHERE created_at < '2025-01-01';
INSERT INTO log (note) SELECT 'rebuilt:' || name FROM rebuilt_items;

INSERT … ON CONFLICT (UPSERT)

RM-0.6.3.7 (RFC-2026-497, S9): single-column conflict target (or default PK) is supported. EXCLUDED.<col> refers to the proposed row.

-- DO NOTHING (earlier behavior, now accepts an explicit target):
INSERT INTO t (id, v) VALUES (1, 100) ON CONFLICT (id) DO NOTHING;

-- DO UPDATE SET ... [WHERE ...]
INSERT INTO counters (key, n)
VALUES ('hits', 1)
ON CONFLICT (key) DO UPDATE SET n = counters.n + EXCLUDED.n;

-- WHERE-filter on existing row (PG-semantics: conflict + WHERE-false = silent no-op):
INSERT INTO inventory (sku, qty)
VALUES ('A', 5)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
WHERE inventory.qty < EXCLUDED.qty;

Not supported:

• multi-column target (ON CONFLICT (a, b)) → 0A000;
• ON CONFLICT ON CONSTRAINT <name> → 0A000.

UPDATE

UPDATE t SET v = 999 WHERE id = 1;

Since RM-0.6.5.5, UPDATE SET supports functional expressions and type casting:

• UPDATE t SET write_date = NOW() WHERE id = 1;
• UPDATE t SET ts = '2026-05-07 14:30:00'::timestamp WHERE id = 1;
• UPDATE t SET day = date_trunc('day', NOW()) WHERE id = 1;

Supported are NOW(), CURRENT_TIMESTAMP, CURRENT_DATE, date_trunc(), and explicit CAST (syntax ::).

DELETE

DELETE FROM t WHERE id = 2;

RETURNING

RM-0.6.3.7 (RFC-2026-497, S10): RETURNING is supported for multi-row INSERT, UPDATE, DELETE. The projection allows *, an explicit column list, and expressions. For INSERT ... ON CONFLICT DO UPDATE, the post-update row is returned (like in PostgreSQL).

INSERT INTO t (v) VALUES (100), (200), (300) RETURNING id, v;
UPDATE t SET v = v + 1 WHERE v > 0 RETURNING id, v AS new_v;
DELETE FROM t WHERE v IS NULL RETURNING id;

Sequence functions

RM-0.6.3.7 (RFC-2026-497): nextval / currval / setval for SEQUENCE objects (see ddl.md — “CREATE / ALTER / DROP SEQUENCE” section). Non-transactional: gap-on-rollback is the correct behavior.

SELECT nextval('s1');                  -- 1, 2, 3, ...; overflow without CYCLE → 2200H
SELECT currval('s1');                  -- last value issued in THIS session
SELECT setval('s1', 100);              -- last_value=100, is_called=true; next nextval = 101
SELECT setval('s1', 50, false);        -- next nextval = 50

currval is session-bound: it returns the value of the last nextval (or setval(_, _, true)) in the same pgwire session. If nextval has not yet been called in the current session — 55000 object_not_in_prerequisite_state, regardless of whether nextval was called in other sessions.

SERIAL / IDENTITY

SERIAL / BIGSERIAL and GENERATED [ALWAYS|BY DEFAULT] AS IDENTITY automatically create an owned sequence <table>_<col>_seq, which is dropped when the table is dropped.

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('alice'), ('bob') RETURNING id;
-- id is populated via nextval('users_id_seq')

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount NUMERIC
);
INSERT INTO orders (id, amount) VALUES (42, 100);
-- → 428C9 generated_always: id column is GENERATED ALWAYS

CREATE TABLE invoices (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  total NUMERIC
);
INSERT INTO invoices (id, total) VALUES (DEFAULT, 250) RETURNING id;
INSERT INTO invoices (id, total) VALUES (1000, 250);     -- explicit OK

SELECT

SELECT id, v FROM t WHERE v > 50 ORDER BY id;

For details on queries (CTE, JOIN, GROUP BY, etc.) — see queries.md.

TRUNCATE

TRUNCATE TABLE t;

TRUNCATE is a DDL reset of the table. For append-only tables, this is the only way to delete data.

Mutation policy enforcement

Mutation policy controls the allowed DML operations on a table.

append_only

no_delete

unrestricted

All DML operations are allowed (default behavior).

Autocommit vs explicit transactions

By default, every DML query runs in autocommit mode (an implicit transaction, committed immediately).

For explicit transactions:

BEGIN;
INSERT INTO t (id, v) VALUES (10, 1000);
UPDATE t SET v = 2000 WHERE id = 10;
COMMIT;

ROLLBACK aborts all changes of the current transaction.

Expected SQLSTATE

Links

• DDL (CREATE TABLE, mutation policy options): ddl.md
• Queries (SELECT details): queries.md
• Partitioning: partitioning.md
• Known issues: Known issues