"MERGE in PostgreSQL: Conditional Upserts Beyond ON CONFLICT"
"postgresql merge statement tutorial — WHEN MATCHED / WHEN NOT MATCHED with conditions, MERGE vs INSERT ON CONFLICT, conditional deletes, and fixing the 'cannot affect row a second time' error."
INSERT ... ON CONFLICT handles the simple case: a row exists, update it; it doesn't, insert it. But real sync logic is conditional — update the price only if the new feed's timestamp is newer, delete products missing from the supplier feed, insert new ones, and skip rows that haven't changed. This postgresql merge statement tutorial covers MERGE (available since PostgreSQL 15): one statement with WHEN MATCHED / WHEN NOT MATCHED branches, each with its own conditions, that reads like the business rule it implements. Last updated: October 4, 2026 What you'll learn The MERGE INTO ... USING ... ON syntax and what each clause does WHEN MATCHED / WHEN NOT MATCHED with per-branch conditions Conditional deletes inside MERGE ( WHEN MATCHED THEN DELETE ) When to use MERGE vs INSERT ON CONFLICT (and when not to use MERGE at all) Fixing the MERGE command cannot affect row a second time cardinality violation PostgreSQL 17 additions: RETURNING , merge_action() , WHEN NOT MATCHED BY SOURCE The Shape of MERGE: INTO, USING, ON, WHEN MERGE compares a target table against a source (a table, a CTE, or a VALUES list) on a join condition, then executes exactly one action per source row: MERGE INTO products AS p USING supplier_feed AS s ON p.sku = s.sku WHEN MATCHED THEN UPDATE SET price = s.price, updated_at = now() WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price); Read it as English: "for each feed row, if a product with that SKU exists, update its price; if not, insert it." The USING clause can be anything a SELECT can read — most real-world MERGE statements use a CTE or VALUES as the source rather than a physical staging table: MERGE INTO products AS p USING (VALUES ('SKU-1', 'Widget', 9.99), ('SKU-2',…