MERGE in PostgreSQL 15+: MATCHED / NOT MATCHED, Upserts, and Table Sync

MERGE is a single statement that performs INSERT, UPDATE, or DELETE in one pass, depending on whether a matching row exists in the target table. PostgreSQL didn't have it before version 15, so everyone leaned on INSERT ... ON CONFLICT. Now we have the real, SQL-standard MERGE, and it covers cases ON CONFLICT simply can't: syncing two tables, deleting stragglers, and applying different actions under different conditions.

Let's see how it's wired and where the line between MERGE and ON CONFLICT actually falls.

Anatomy: target, source, and WHEN branches

MERGE takes a target table (MERGE INTO), a source (USING), and a join condition (ON). Then come the WHEN MATCHED branch (the row matched on ON) and the WHEN NOT MATCHED branch (present in source, missing in target).

Take a schema with a users table and an incoming batch users_staging.

MERGE INTO users AS u
USING users_staging AS s
   ON u.id = s.id
WHEN MATCHED THEN
  UPDATE SET email = s.email,
             name  = s.name,
             updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (id, email, name, created_at)
  VALUES (s.id, s.email, s.name, now());

Key things to internalize:

• The source can be a table, a subquery, or a VALUES list.
• Branches are evaluated top to bottom; the first matching one fires.
• You cannot reference EXCLUDED inside a branch — that's ON CONFLICT syntax and doesn't exist in MERGE. Just write s.email, s.name.
• MERGE returns no rows on older versions; RETURNING only arrived in PostgreSQL 17.

Conditional branches and DELETE

The real power of MERGE is the extra AND conditions on each branch, plus the fact you can have several branches. This is exactly what ON CONFLICT cannot reproduce.

MERGE INTO orders AS o
USING incoming_orders AS i
   ON o.order_id = i.order_id
WHEN MATCHED AND i.status = 'cancelled' THEN
  DELETE
WHEN MATCHED AND o.amount <> i.amount THEN
  UPDATE SET amount = i.amount,
             status = i.status,
             updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status)
  VALUES (i.order_id, i.customer_id, i.amount, i.status);

Three different outcomes in one statement: a cancelled order gets deleted, a changed one gets updated (only when the amount actually differs), and a new one gets inserted. You can also add WHEN NOT MATCHED ... AND ... to filter which inserts happen.

Handy moves:

• WHEN MATCHED AND ... THEN DO NOTHING — explicitly skip a row.
• WHEN NOT MATCHED BY SOURCE THEN DELETE — remove from the target what's absent in the source (PostgreSQL 17; perfect for a full sync).
• Branch order is priority order. Put narrow conditions above broad ones.

Full sync of two tables

A classic job: bring an employees table into line with an export from an HR system, hr_feed — add newcomers, update changes, terminate the missing.

MERGE INTO employees AS e
USING hr_feed AS f
   ON e.emp_id = f.emp_id
WHEN MATCHED AND (e.department, e.salary) IS DISTINCT FROM (f.department, f.salary) THEN
  UPDATE SET department = f.department,
             salary     = f.salary
WHEN NOT MATCHED THEN
  INSERT (emp_id, full_name, department, salary)
  VALUES (f.emp_id, f.full_name, f.department, f.salary)
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET status = 'terminated';

IS DISTINCT FROM guards against pointless UPDATEs when nothing changed (and compares NULL correctly). NOT MATCHED BY SOURCE covers the third corner — rows that exist only in the target.

MySQL has no standalone MERGE; you use INSERT ... ON DUPLICATE KEY UPDATE. ClickHouse takes a different route entirely: insert plus ReplacingMergeTree/engine logic, with deduplication deferred to a background merge.

MERGE vs ON CONFLICT: which to pick

Both can upsert, but they solve it differently.

• ON CONFLICT triggers on a unique index or constraint violation. No suitable index, no upsert.
• MERGE matches on an arbitrary ON condition. An index isn't required (though it helps performance).
• ON CONFLICT is atomic against concurrent inserts: under a race it reliably catches the conflict. MERGE under high concurrency can fail with a unique violation — it does not do any skip-locked magic.
• MERGE can DELETE and run multiple distinct branches; ON CONFLICT only does "insert or update one row".

A practical rule of thumb:

-- Simple counter / idempotent insert — reach for ON CONFLICT
INSERT INTO page_views (page_id, views)
VALUES (42, 1)
ON CONFLICT (page_id)
DO UPDATE SET views = page_views.views + 1;

For a hot, concurrent single-key upsert, ON CONFLICT is simpler and safer. Reach for MERGE when you need "multiple branches + DELETE + sync" logic — i.e. batch processing and ETL.

Gotcha. MERGE does not protect you from races on its own. If two sessions merge the same not-yet-existing key at once, both take the NOT MATCHED branch and one hits a unique_violation. For concurrent workloads, wrap it in a retry or use ON CONFLICT. And remember: RETURNING from MERGE is only available from PostgreSQL 17 onward.