Upserts With ON CONFLICT

Atomic, idempotent writes

INSERT ... ON CONFLICT (key) DO UPDATE inserts new rows and updates existing ones atomically, using the special EXCLUDED row for the proposed values. DO NOTHING skips conflicting rows. It requires a unique constraint or index on the conflict target. Upserts make imports, counters and synchronisation jobs safe to retry. PostgreSQL 15 also added the SQL-standard MERGE statement for more complex cases.

Adding stock with an upsert, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Product 1 already had 50 units, so the conflict branch adds 5 to make 55; product 2 is new and inserted with 7. RETURNING shows both results.

CREATE TABLE stock (product_id bigint PRIMARY KEY REFERENCES products(id), qty int NOT NULL);
INSERT INTO stock VALUES (1, 50);

INSERT INTO stock (product_id, qty) VALUES (1, 5), (2, 7)
ON CONFLICT (product_id) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING product_id, qty;

Output:

 product_id | qty 
------------+-----
          1 |  55
          2 |   7
(2 rows)

Make jobs idempotent

Upserts let a failed import be re-run without creating duplicates.

Quick check: What does EXCLUDED refer to in ON CONFLICT DO UPDATE?

• The deleted row
• The row that was proposed for insertion
• A table of excluded users
• The previous transaction