MERGE and Upserts

WHEN MATCHED and WHEN NOT MATCHED

The SQL-standard MERGE (PostgreSQL 15 and later) compares a source with a target and, per row, updates, deletes or inserts according to WHEN MATCHED and WHEN NOT MATCHED clauses with optional extra conditions. It suits batch synchronisation such as applying a delivery to stock. For single-row concurrent upserts, PostgreSQL's INSERT ... ON CONFLICT DO UPDATE is simpler and handles concurrent inserts atomically.

Writes you can trust

MERGE, joined updates and transactions make data changes precise and reversible until committed.

Applying a delivery to stock, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Pen is topped up to 150, lamp (0 in stock and 0 delivered) is deleted, desk is inserted and mug is untouched.

MERGE INTO stock s
USING delivery d ON s.product = d.product
WHEN MATCHED AND d.qty = 0 AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = s.qty + d.qty
WHEN NOT MATCHED THEN INSERT (product, qty) VALUES (d.product, d.qty);

SELECT * FROM stock ORDER BY product;

Output:

 product | qty 
---------+-----
 desk    |   3
 mug     |   5
 pen     | 150
(3 rows)

Order WHEN clauses from specific to general

The first matching WHEN clause wins, so put the conditional DELETE before the general UPDATE.

Quick check: Which PostgreSQL feature is simplest for a concurrent single-row upsert?

• CREATE VIEW
• A SELECT then INSERT in application code
• TRUNCATE
• INSERT ... ON CONFLICT DO UPDATE