INSERT ... ON CONFLICT, MERGE, RETURNING, and Observable DML Workflows

Learning outcomes

ServiceHub receives inventory snapshots from a field warehouse. Some SKUs already exist, some are new, and one old SKU disappeared from the incoming snapshot. A robust synchronization workflow must define unique conflict identity, action ordering, rows that are actually changed, returned old/new values, privileges, and what concurrent writers are allowed to do. “Use an upsert” is not a complete contract.

1. Chapter 06 lab bootstrap

All five lessons use one small, deterministic ServiceHub dataset. Run this in the disposable servicehub_lab database as servicehub_owner or another role that owns the app schema. The objects are intentionally prefixed ch06_ so Chapter 06 can be reset without touching earlier chapters.

\echo 'Verify target before recreating Chapter 06 objects'SELECT current_database(), current_user;\dt app.ch06_*DROP VIEW IF EXISTS app.ch06_region_team_report;DROP AGGREGATE IF EXISTS app.ch06_sum_squares(numeric);DROP FUNCTION IF EXISTS app.ch06_add_square(numeric, numeric);DROP TABLE IF EXISTS app.ch06_inventory_stage;DROP TABLE IF EXISTS app.ch06_inventory;DROP TABLE IF EXISTS app.ch06_daily_metric;DROP TABLE IF EXISTS app.ch06_work_order;CREATE TABLE app.ch06_work_order (    work_order_id integer PRIMARY KEY,    customer_id integer NOT NULL,    region text,    team_code text NOT NULL,    status text NOT NULL CHECK (status IN ('open','closed','cancelled')),    priority smallint NOT NULL CHECK (priority BETWEEN 1 AND 4),    opened_on date NOT NULL,    closed_on date,    actual_minutes integer CHECK (actual_minutes >= 0),    cost numeric(10,2) NOT NULL CHECK (cost >= 0));INSERT INTO app.ch06_work_order VALUES(1, 1,'North',  'A','closed',   1,'2026-08-01','2026-08-01', 40,200.00),(2, 2,'North',  'A','closed',   2,'2026-08-01','2026-08-02', 90,150.00),(3, 3,'North',  'B','closed',   1,'2026-08-02','2026-08-02', 60,300.00),(4, 4,'South',  'B','open',     2,'2026-08-02',NULL,          NULL,120.00),(5, 4,'South',  'B','closed',   3,'2026-08-03','2026-08-04',180,500.00),(6, 5,'South',  'C','closed',   1,'2026-08-03','2026-08-03', 30, 80.00),(7, 1,'Central','A','closed',   2,'2026-08-04','2026-08-05',120,250.00),(8, 6,'Central','C','open',     1,'2026-08-04',NULL,          NULL,110.00),(9, 7,NULL,      'C','closed',  2,'2026-08-05','2026-08-05', 75,130.00),(10,2,'North',   'A','closed',  3,'2026-08-05','2026-08-06',210,600.00),(11,8,'South',   'C','cancelled',4,'2026-08-06','2026-08-06',10,  0.00),(12,3,'Central', 'B','closed',  1,'2026-08-06','2026-08-07', 50,175.00);CREATE TABLE app.ch06_daily_metric (    metric_id integer PRIMARY KEY,    metric_date date NOT NULL,    region text,    completed_count integer NOT NULL CHECK (completed_count >= 0),    revenue numeric(10,2) NOT NULL CHECK (revenue >= 0),    UNIQUE NULLS NOT DISTINCT (metric_date, region));INSERT INTO app.ch06_daily_metric VALUES(1,'2026-08-01','North',   1,200.00),(2,'2026-08-02','North',   2,450.00),(3,'2026-08-03','South',   1, 80.00),(4,'2026-08-04','South',   1,500.00),(5,'2026-08-05','Central', 1,250.00),(6,'2026-08-05',NULL,      1,130.00),(7,'2026-08-06','North',   1,600.00),(8,'2026-08-07','Central', 1,175.00);CREATE TABLE app.ch06_inventory (    sku text PRIMARY KEY,    description text NOT NULL,    on_hand integer NOT NULL CHECK (on_hand >= 0),    reorder_point integer NOT NULL CHECK (reorder_point >= 0),    active boolean NOT NULL DEFAULT true,    updated_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch06_inventory (sku, description, on_hand, reorder_point) VALUES('A-100','Fuse kit',5,5),('B-200','Patch cable',20,10),('C-300','Pump seal',7,3);CREATE TABLE app.ch06_inventory_stage (    sku text PRIMARY KEY,    description text NOT NULL,    on_hand integer NOT NULL CHECK (on_hand >= 0),    reorder_point integer NOT NULL CHECK (reorder_point >= 0));INSERT INTO app.ch06_inventory_stage VALUES('A-100','Fuse kit',8,5),('B-200','Patch cable',0,10),('D-400','Sensor battery',12,4);

The seed contains nine closed work orders, two open work orders, one cancelled work order, a real NULL region, repeated dates, and a small inventory snapshot. Those details create observable edge cases for aggregate NULL handling, peer-aware window frames, subtotal NULLs, and DML synchronization.

2. ON CONFLICT begins with a unique arbiter

ON CONFLICT reacts to a conflict with a unique/exclusion arbiter. For DO UPDATE, PostgreSQL can infer a matching unique index from the columns/expressions and optional predicate in the conflict target. The proposed row is available through the special EXCLUDED relation.

INSERT INTO app.ch06_inventory AS i       (sku, description, on_hand, reorder_point)VALUES ('A-100','Fuse kit',8,5)ON CONFLICT (sku) DO UPDATESET description   = EXCLUDED.description,    on_hand       = EXCLUDED.on_hand,    reorder_point = EXCLUDED.reorder_point,    active        = true,    updated_at    = clock_timestamp()RETURNING sku,          old.on_hand AS old_on_hand,          new.on_hand AS new_on_hand,          old.active AS old_active,          new.active AS new_active;

sku   | old_on_hand | new_on_hand | old_active | new_active------+-------------+-------------+------------+-----------A-100 | 5           | 8           | t          | t

If the row were newly inserted, old target values would be NULL. On a conflicting DO UPDATE, OLD can expose the previous row while NEW exposes the post-update row. This removes a common application race where code performs a separate SELECT merely to discover what changed.

3. DO NOTHING and conditional DO UPDATE have observable row counts

INSERT INTO app.ch06_inventory (sku, description, on_hand, reorder_point)VALUES ('A-100','Fuse kit',8,5)ON CONFLICT (sku) DO NOTHINGRETURNING sku;INSERT INTO app.ch06_inventory AS i (sku, description, on_hand, reorder_point)VALUES ('A-100','Fuse kit',8,5)ON CONFLICT (sku) DO UPDATESET on_hand = EXCLUDED.on_hand,    updated_at = clock_timestamp()WHERE i.on_hand IS DISTINCT FROM EXCLUDED.on_handRETURNING sku, old.on_hand, new.on_hand;

DO NOTHING query -> 0 returned rowsconditional DO UPDATE query -> 0 returned rows

Rows locked by ON CONFLICT DO UPDATE but rejected by its WHERE condition are not returned. Applications should treat the RETURNING row set and command outcome as evidence, not assume every proposed row changed something.

4. Reset inventory before the MERGE synchronization

TRUNCATE app.ch06_inventory;INSERT INTO app.ch06_inventory (sku, description, on_hand, reorder_point) VALUES('A-100','Fuse kit',5,5),('B-200','Patch cable',20,10),('C-300','Pump seal',7,3);SELECT sku, on_hand, activeFROM app.ch06_inventoryORDER BY sku;

5. MERGE classifies candidate rows, then evaluates WHEN clauses in order

PostgreSQL first joins source to target and classifies each candidate as MATCHED, NOT MATCHED BY TARGET, or NOT MATCHED BY SOURCE. For each candidate, WHEN clauses of the relevant kind are tested in written order and the first true action is executed. At most one action executes for a candidate row.

MERGE INTO app.ch06_inventory AS tUSING app.ch06_inventory_stage AS sON s.sku = t.skuWHEN MATCHED AND (       t.description IS DISTINCT FROM s.description    OR t.on_hand IS DISTINCT FROM s.on_hand    OR t.reorder_point IS DISTINCT FROM s.reorder_point    OR t.active IS NOT TRUE) THEN  UPDATE SET description = s.description,             on_hand = s.on_hand,             reorder_point = s.reorder_point,             active = true,             updated_at = clock_timestamp()WHEN MATCHED THEN  DO NOTHINGWHEN NOT MATCHED BY TARGET THEN  INSERT (sku, description, on_hand, reorder_point, active)  VALUES (s.sku, s.description, s.on_hand, s.reorder_point, true)WHEN NOT MATCHED BY SOURCE THEN  UPDATE SET active = false,             updated_at = clock_timestamp()RETURNING merge_action() AS action,          COALESCE(new.sku, old.sku) AS sku,          old.on_hand AS old_on_hand,          new.on_hand AS new_on_hand,          old.active AS old_active,          new.active AS new_active;

UPDATE A-100: old 5  -> new 8,  active t -> tUPDATE B-200: old 20 -> new 0,  active t -> tUPDATE C-300: old 7  -> new 7,  active t -> fINSERT D-400: old NULL -> new 12, old active NULL -> new active t

Do not treat RETURNING row order as a processing sequence unless you build and document a separate ordering layer where supported. The deterministic acceptance check is the target table's final state.

SELECT sku, description, on_hand, reorder_point, activeFROM app.ch06_inventoryORDER BY sku;

sku   | on_hand | reorder_point | active------+---------+---------------+-------A-100 | 8       | 5             | tB-200 | 0       | 10            | tC-300 | 7       | 3             | fD-400 | 12      | 4             | t

6. Failure analysis: duplicate source matches are not “last row wins”

A correct MERGE source should produce at most one candidate change row for each target row. If two source rows attempt to update/delete the same target row, PostgreSQL can raise a cardinality violation; repeated inserts can cause uniqueness violations. Deduplicate or constrain the staging source before MERGE rather than hoping source order defines a winner.

SELECT sku, count(*)FROM app.ch06_inventory_stageGROUP BY skuHAVING count(*) > 1;-- The PRIMARY KEY on staging makes the expected result zero rows.-- If a raw import table lacks that constraint, reject or deterministically-- reconcile duplicates before it becomes the MERGE source.

7. Concurrency: ON CONFLICT and MERGE are related, not interchangeable

INSERT ... ON CONFLICT DO UPDATE is designed around a unique conflict arbiter and has specific behavior when a concurrent insert creates that conflict. MERGE is a broader source-target synchronization statement; ordinary transaction-isolation rules apply and concurrent target changes can alter outcomes or raise errors. PostgreSQL's MERGE documentation explicitly suggests considering ON CONFLICT when the requirement is “update if a concurrent insert occurs.”

For concurrent production workflows, test at the real isolation level, define retryable SQLSTATEs, preserve idempotency, and verify business invariants after retries. Chapter 07 will cover MVCC, isolation, locks, serialization, and retry design in depth.

8. Privileges are checked for the statement's possible actions

ON CONFLICT DO UPDATE requires INSERT plus the UPDATE/SELECT privileges needed by expressions and conflict targets. MERGE has no single MERGE privilege: the statement needs the INSERT/UPDATE/DELETE/SELECT privileges required by its specified actions and expressions, and PostgreSQL checks them at statement start even if a particular WHEN branch does not execute for the current data.

SELECT current_user,       has_table_privilege(current_user,'app.ch06_inventory','SELECT') AS can_select,       has_table_privilege(current_user,'app.ch06_inventory','INSERT') AS can_insert,       has_table_privilege(current_user,'app.ch06_inventory','UPDATE') AS can_update,       has_table_privilege(current_user,'app.ch06_inventory','DELETE') AS can_delete;

The owner lab role will normally report true. Do not grant broad privileges merely to make a synchronization example work; production ingestion roles should be scoped to the objects and operations they actually require.

9. Hands-on acceptance checklist

After experimentation, rerun the Chapter 06 bootstrap if later lessons need the original inventory seed. The reporting lesson primarily reads ch06_work_order, so inventory changes do not affect its results.

10. Bridge to reusable reporting

RETURNING turns writes into observable row streams. Lesson 5 applies the same compositional principle to reads: a report will have explicit relational layers, stable view columns, deterministic JSON element order, and an evidence-based decision boundary between ordinary views and precomputation.

Authoritative references

• PostgreSQL 18 — INSERT / ON CONFLICT / RETURNING
• PostgreSQL 18 — MERGE
• PostgreSQL 18 — Returning Data from Modified Rows
• PostgreSQL 18 — Unique Indexes
• PostgreSQL 18 — Transaction Isolation