Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 90 ยท Querying with PostgreSQL Features

MERGE in PostgreSQL

Learn MERGE in PostgreSQL 15+: WHEN MATCHED and NOT MATCHED branches, DELETE, NOT MATCHED BY SOURCE, RETURNING, concurrency and when to use ON CONFLICT instead.

Real systems constantly synchronize two sets of rows: a nightly file of price changes, a feed from a partner, a staging table filled by an import. Some of the rows already exist and need updating. Some are new and need inserting. Some should disappear. Before PostgreSQL 15 you wrote that as several statements, or you squeezed the common case into INSERT ... ON CONFLICT. Since version 15 PostgreSQL has the SQL-standard MERGE statement, which describes all the outcomes in one command.

UPSERT and RETURNING already introduced MERGE briefly. This lesson is the full tour: the syntax, every kind of WHEN branch, the rules and errors you will meet, what changed in PostgreSQL 17 and 18, how concurrency works, and when ON CONFLICT is still the better choice. MERGE needs a PostgreSQL server (SQLite has no MERGE), so the PostgreSQL blocks are read-only. The live examples reproduce the decisions MERGE makes using ordinary SQL, which also makes the semantics easy to see.

MERGE in one picture

Target The table you change.
Source A table, query or VALUES list.
ON How a source row finds its target row.
WHEN branches Update, delete, insert or skip.

Each source row takes the first branch that fits.

1 / 2

The basic shape

SQL
-- PostgreSQL 15+ (read-only)
MERGE INTO stock AS t
USING deliveries AS s
   ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN
  INSERT (sku, qty) VALUES (s.sku, s.qty);

Read it in four parts:

  1. MERGE INTO target names the table (or, since PostgreSQL 17, an updatable view) that will change. You can give it an alias.
  2. USING source is where the new data comes from. It can be a table, a subquery, a VALUES list or a function result.
  3. ON condition says how a source row finds its partner in the target. This is a join condition. It is usually an equality on the key.
  4. WHEN branches say what to do for each source row, depending on whether a partner was found.

For every source row PostgreSQL looks for target rows that satisfy the ON condition. If it finds one, the row is matched and the WHEN MATCHED branches apply. If it finds none, the row is not matched and the WHEN NOT MATCHED branches apply. The result is reported as the command tag MERGE n, where n is the number of rows inserted, updated or deleted.

The branches

WHEN MATCHED

The actions allowed for a matched row are UPDATE, DELETE and DO NOTHING:

SQL
-- PostgreSQL 15+ (read-only)
WHEN MATCHED AND s.discontinued THEN DELETE
WHEN MATCHED AND s.qty = 0      THEN DO NOTHING
WHEN MATCHED                    THEN UPDATE SET qty = t.qty + s.qty

Each branch can carry an extra AND condition. PostgreSQL tests the branches in the order written and runs the first whose condition is true; later ones are ignored for that row. So put the specific cases first and the general case last. If no branch fits, the row is left alone.

In UPDATE SET you can use columns of both the target (t.qty) and the source (s.qty). You cannot write the target table name before a column on the left of SET, and you do not repeat the table name after UPDATE.

WHEN NOT MATCHED

For a source row without a partner you can INSERT or DO NOTHING:

SQL
-- PostgreSQL 15+ (read-only)
WHEN NOT MATCHED AND s.discontinued THEN DO NOTHING
WHEN NOT MATCHED THEN
  INSERT (sku, qty) VALUES (s.sku, s.qty)

The INSERT here has only a single VALUES row (or DEFAULT VALUES). It cannot use a SELECT of its own and cannot have an ON CONFLICT clause, because the source is already the thing that provides rows. A bare WHEN NOT MATCHED means WHEN NOT MATCHED BY TARGET: the source row has no partner in the target.

WHEN NOT MATCHED BY SOURCE (PostgreSQL 17)

Starting with PostgreSQL 17 you can also act on target rows that no source row matched. This is how you express "delete everything that is not in the feed":

SQL
-- PostgreSQL 17+ (read-only)
WHEN NOT MATCHED BY SOURCE AND t.qty = 0 THEN DELETE
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET active = false

Because there is no source row in these branches, you can only use target columns in the conditions and in UPDATE SET. This branch lets one statement do a full "synchronize this table with that list": update the matching rows, insert the new ones, and retire the missing ones. Before version 17 you needed a separate DELETE ... WHERE NOT EXISTS.

RETURNING and merge_action()

PostgreSQL 17 added RETURNING to MERGE. The function merge_action() tells you which branch produced each returned row:

SQL
-- PostgreSQL 17+ (read-only)
MERGE INTO stock AS t
USING deliveries AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action() AS action, t.sku, t.qty;
-- action is 'INSERT', 'UPDATE' or 'DELETE'

PostgreSQL 18 extends RETURNING in INSERT, UPDATE, DELETE and MERGE so it can name OLD and NEW values, which gives you the before and after state of each change.

The rule that surprises people: one match per target row

A target row may be affected at most once by a single MERGE. If two source rows both match the same target row, PostgreSQL stops with an error rather than guess which one wins:

TEXT
ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

This is usually a data problem: the feed has duplicates. Deduplicate the source before merging:

SQL
-- PostgreSQL 15+ (read-only)
MERGE INTO stock AS t
USING (
  SELECT DISTINCT ON (sku) sku, qty
  FROM deliveries
  ORDER BY sku, received_at DESC          -- keep the newest row per sku
) AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

(The DISTINCT ON trick is explained in DISTINCT ON and Top-N.)

Seeing the decisions without MERGE

The browser database is SQLite, which cannot run MERGE. But you can reproduce the logic. The next example holds a stock table and a feed of changes. A query first shows which branch each row would take (the heart of MERGE), then four ordinary statements apply those decisions in the same order the WHEN clauses were written, inside one transaction.

The rules being imitated are:

  • matched and discontinued: DELETE
  • matched: UPDATE qty = qty + delta
  • not matched and discontinued: DO NOTHING
  • not matched: INSERT
  • not matched by source and quantity is zero: DELETE
SQL MERGE decisions rebuilt with plain SQL

Press Run (or Ctrl + Enter) to see the result here.

The first result lists what happens to each key. The final table shows A1 updated to 15, B2 deleted, C3 removed because it had no feed row and zero stock, D4 untouched and E5 inserted. F6 was discontinued and new, so nothing happened to it.

Notice what the four statements cannot guarantee: another session could change stock between them, and you have to keep their order consistent with your intended WHEN order yourself. MERGE evaluates the whole thing against one snapshot in a single statement, which is its main practical advantage.

Triggers, permissions and limits

  • Triggers. Row-level triggers fire for the action actually taken (an insert fires INSERT triggers, an update fires UPDATE triggers). Statement-level triggers fire for each kind of action that appears in the MERGE text, even if no row used that branch.
  • Privileges. You need SELECT on the source and on any target column used in conditions, plus INSERT, UPDATE or DELETE privileges for the actions you wrote. See Roles and Privileges.
  • Not available on tables that have rules, or on foreign tables and some special relations; check the documentation for your version.
  • Large merges behave like any big write: they generate WAL and dead tuples, so see Vacuum and Autovacuum after a big one, and consider batching.

Concurrency: where MERGE and ON CONFLICT differ

Under the default READ COMMITTED isolation, if a concurrent transaction changes a matched target row while your MERGE is waiting for it, PostgreSQL re-evaluates the conditions against the newest version of that row and may choose a different branch. If the row was deleted meanwhile, the source row is treated as not matched and may be inserted.

What MERGE does not do is retry on a unique violation. If two sessions each MERGE the same new key at the same moment, both see "not matched", both try to insert, and the second one fails with a unique-constraint error. INSERT ... ON CONFLICT was designed for exactly this race: it atomically either inserts or updates and never raises that error for the conflict you named. This is the single most important difference.

INSERT ... ON CONFLICT MERGE
Introduced PostgreSQL 9.5 PostgreSQL 15
Needs a unique index Yes, as the conflict target No, any join condition works
Outcomes insert, update or nothing insert, update, delete or nothing, several conditional branches
Race on a new key Safe, no error Can raise a unique violation
Matches on the index (a key) any condition
Standard SQL No, PostgreSQL syntax Yes, SQL:2003
Best for One-row-style upserts under concurrency Batch synchronization with rules, and portable code

A reasonable rule: use ON CONFLICT for key-based upserts from many concurrent writers, and MERGE for set-based synchronization where you need deletes, conditions or standard syntax. If you must use MERGE with concurrent writers, be ready to retry on the unique-violation error, or serialize the writers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A MERGE has these branches in order: WHEN MATCHED AND s.discontinued THEN DELETE, then WHEN MATCHED THEN UPDATE .... A matched source row has discontinued = true. What happens?

    Branches are tested in order, and only the first one whose condition is true is executed for that row.
  2. 2. Two source rows have the same key and both match one target row. What does MERGE do?

    PostgreSQL requires at most one source row per target row. Deduplicate the source first, for example with DISTINCT ON.
  3. 3. Which clause, added in PostgreSQL 17, lets MERGE act on target rows that no source row matched?

    WHEN NOT MATCHED BY SOURCE handles unmatched target rows, for example deleting or deactivating them.
  4. 4. Why is INSERT ... ON CONFLICT usually preferred for concurrent upserts of single keys?

    ON CONFLICT relies on a unique index and handles the race safely. Two concurrent MERGE statements may both choose the insert branch and one will hit a unique violation.

Summary

  • MERGE INTO target USING source ON condition applies a set of source rows to a target table with several outcomes in one statement (PostgreSQL 15+).
  • WHEN MATCHED allows UPDATE, DELETE and DO NOTHING. WHEN NOT MATCHED allows INSERT and DO NOTHING. Branches carry optional AND conditions and run in written order, first match wins.
  • PostgreSQL 17 added WHEN NOT MATCHED BY SOURCE, RETURNING with merge_action() and updatable views as targets. PostgreSQL 18 adds OLD and NEW in RETURNING.
  • A target row can be affected only once per statement, so deduplicate the source.
  • MERGE does not retry on concurrent inserts of the same key. Use ON CONFLICT for concurrent single-key upserts and MERGE for batch synchronization.

Back to the single-statement upsert in UPSERT and RETURNING, or on to Full-Text Search.