INSERT … ON CONFLICT and MERGE

Insert a row, or update/skip it if a row with the same key already exists.

Syntax

INSERT INTO t (key, col) VALUES (...)
ON CONFLICT (key) DO NOTHING;

INSERT INTO t (key, col) VALUES (...)
ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col [WHERE ...];

MERGE INTO target t
USING source s ON t.key = s.key
WHEN MATCHED [AND cond] THEN UPDATE SET ... | DELETE | DO NOTHING
WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
[WHEN NOT MATCHED BY SOURCE THEN ...]
[RETURNING ...];

ON CONFLICT (PostgreSQL's upsert):

The conflict target (key) must match a unique index or constraint (or use ON CONSTRAINT name).
EXCLUDED is the row that was proposed for insertion; the existing row is referenced by the table name/alias.
DO NOTHING skips conflicting rows silently. It is atomic and safe under concurrency.
One statement cannot update the same row twice: duplicates of the key inside the inserted data cause ON CONFLICT DO UPDATE command cannot affect row a second time.

MERGE (SQL standard, PostgreSQL 15+) compares a source with a target and runs different actions for matched and unmatched rows. WHEN NOT MATCHED BY SOURCE (17+) handles target rows absent from the source. RETURNING and merge_action() are available since 17. MERGE is not a concurrency-safe upsert the way ON CONFLICT is.

Examples

Upsert stock levels (modifies data) starter

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (1, 'Arabic Coffee Beans 1kg', 'Grocery', 89, 40),
       (99, 'Saffron 10g', 'Grocery', 120, 15)
ON CONFLICT (product_id) DO UPDATE
SET stock = products.stock + EXCLUDED.stock
RETURNING product_id, product_name, stock, (xmax <> 0) AS was_update;

Product 1 exists, so its stock increases; product 99 is inserted. The xmax trick distinguishes the two.

MERGE a price list (modifies data) starter

MERGE INTO products p
USING (VALUES (2, 150.00), (3, 199.00), (50, 60.00)) AS s(product_id, new_price)
ON p.product_id = s.product_id
WHEN MATCHED AND p.price <> s.new_price THEN
  UPDATE SET price = s.new_price
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, category, price, stock)
  VALUES (s.product_id, 'New product ' || s.product_id, 'Uncategorised', s.new_price, 0)
RETURNING merge_action() AS action, p.product_id, p.price;

Matched rows with a different price are updated; unknown ids are inserted.

Pitfalls

ON CONFLICT on columns without a unique constraint: there is no unique or exclusion constraint matching the ON CONFLICT specification.
Writing SET col = col (keeps the old value) when you meant EXCLUDED.col.
Duplicate keys within one INSERT ... ON CONFLICT DO UPDATE batch.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col) (or a row alias in 8.0.19+); no MERGE.
SQLite: INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col (3.24+); also INSERT OR REPLACE.
SQL Server: MERGE (statement must end with ;); no ON CONFLICT.
Oracle: MERGE.
BigQuery: MERGE.
Snowflake: MERGE.