MERGE: Syncing One Table Into Another

MERGE syncs a target table with a source in one statement: update what matches, insert what’s new, delete what should go. The examples sync a product table with a supplier’s daily feed.

-- setup.sql
CREATE TABLE products (
    sku    TEXT PRIMARY KEY,
    name   TEXT NOT NULL,
    price  NUMERIC(8, 2) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE feed (             -- today's file from the supplier
    sku          TEXT,
    name         TEXT,
    price        NUMERIC(8, 2),
    discontinued BOOLEAN
);
INSERT INTO products VALUES
    ('A1', 'Mug', 8.00, true), ('B2', 'Lamp', 30.00, true),
    ('C3', 'Rug', 55.00, true), ('D4', 'Desk', 120.00, true);
INSERT INTO feed VALUES
    ('A1', 'Mug', 9.50, false),     -- price changed
    ('B2', 'Lamp', 30.00, true),    -- supplier dropped it
    ('E5', 'Chair', 75.00, false);  -- new product

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 3

One Statement, Three Actions

Each WHEN clause handles one case. PostgreSQL tries them in order, and the first one whose condition holds is the one that runs.

-- merge.sql
MERGE INTO products p
USING feed f ON p.sku = f.sku
WHEN MATCHED AND f.discontinued THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
RETURNING merge_action(), p.sku, p.price;
SELECT * FROM products ORDER BY sku;

$ psql -d sqldemo -f merge.sql
 merge_action | sku | price 
--------------+-----+-------
 UPDATE       | A1  |  9.50
 DELETE       | B2  | 30.00
 INSERT       | E5  | 75.00
(3 rows)

MERGE 3
 sku | name  | price  | active 
-----+-------+--------+--------
 A1  | Mug   |   9.50 | t
 C3  | Rug   |  55.00 | t
 D4  | Desk  | 120.00 | t
 E5  | Chair |  75.00 | t
(4 rows)

A1 matched with a new price, so it was updated. B2 matched too, but the first WHEN caught it, because it’s discontinued, so it was deleted. Order matters: put the plain WHEN MATCHED THEN UPDATE first, and B2 would be updated instead, because the discontinued branch would never be reached. E5 had no match, so it was inserted. C3 and D4 aren’t in the feed and nothing touched them. RETURNING merge_action(), new in PostgreSQL 17, reports which branch fired for each row. MERGE 3 is the number of rows changed.

Rows the Source No Longer Has

WHEN NOT MATCHED BY SOURCE (also new in PostgreSQL 17) handles target rows with no matching source row: here, products missing from today’s feed.

-- bysource.sql
MERGE INTO products p
USING feed f ON p.sku = f.sku
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET active = false
RETURNING merge_action(), p.sku, p.active;

$ psql -d sqldemo -f bysource.sql
 merge_action | sku | active 
--------------+-----+--------
 UPDATE       | D4  | f
 UPDATE       | C3  | f
(2 rows)

MERGE 2

C3 and D4 weren’t in the feed, so both were marked inactive. Plain WHEN NOT MATCHED is short for WHEN NOT MATCHED BY TARGET: a source row the target doesn’t have. The two together let one MERGE mirror a source completely. The rows came back as D4 then C3: RETURNING follows the order PostgreSQL processed them in, so add an ORDER BY in a separate query if you need one.

When Two Source Rows Match One Target Row

A feed with the same SKU twice makes the update ambiguous.

-- dupe.sql
INSERT INTO feed VALUES ('A1', 'Mug', 9.00, false);   -- the same SKU twice
MERGE INTO products p
USING feed f ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET price = f.price;
MERGE INTO products p
USING (SELECT DISTINCT ON (sku) * FROM feed ORDER BY sku, price) f ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET price = f.price;

$ psql -d sqldemo -f dupe.sql
INSERT 0 1
psql:dupe.sql:5: ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.
MERGE 2

Two source rows matched A1, at 9.50 and 9.00, so MERGE would have had to update the same row twice. It refuses instead of guessing, and the whole statement does nothing. The fix is to decide which source row wins before merging. DISTINCT ON (sku) ... ORDER BY sku, price keeps the lowest price per SKU, and the second MERGE then updates A1 and E5 once each.

Why ON CONFLICT Still Exists

MERGE looks like an upsert, but it doesn’t handle two sessions inserting the same new key at once. Session 1 merges a new SKU and holds its transaction open; session 2 starts the same MERGE while session 1 is still running.

-- race_a.sql (session 1)
BEGIN;
MERGE INTO products p USING (VALUES ('F6', 'Shelf', 40.00)) s(sku, name, price) ON p.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);
SELECT pg_sleep(2);
COMMIT;

-- race_b.sql (session 2, started while session 1 sleeps)
MERGE INTO products p USING (VALUES ('F6', 'Shelf', 42.00)) s(sku, name, price) ON p.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

Run the two files in separate terminals, starting race_b.sql while race_a.sql is still in its pg_sleep. Run one after the other, race_b would find F6 and take the update branch instead. Session 1’s output comes first here because it finishes first:

$ psql -d sqldemo -f race_a.sql
BEGIN
MERGE 1
 pg_sleep 
----------
 
(1 row)

COMMIT
$ psql -d sqldemo -f race_b.sql
psql:race_b.sql:4: ERROR:  duplicate key value violates unique constraint "products_pkey"
DETAIL:  Key (sku)=(F6) already exists.

When session 2 ran, it couldn’t see session 1’s uncommitted row, so its MERGE took the NOT MATCHED branch and tried to insert. That insert then waited on the unique index until session 1 committed, and failed. MERGE decides which branch to take once, before it inserts, and doesn’t go back and re-check. The same race with INSERT ... ON CONFLICT:

-- race_c.sql (session 1)
BEGIN;
INSERT INTO products (sku, name, price) VALUES ('G7', 'Stool', 25.00)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;
SELECT pg_sleep(2);
COMMIT;

-- race_d.sql (session 2, started while session 1 sleeps)
INSERT INTO products (sku, name, price) VALUES ('G7', 'Stool', 27.00)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;
SELECT sku, price FROM products WHERE sku = 'G7';

$ psql -d sqldemo -f race_c.sql
BEGIN
INSERT 0 1
 pg_sleep 
----------
 
(1 row)

COMMIT
$ psql -d sqldemo -f race_d.sql
INSERT 0 1
 sku | price 
-----+-------
 G7  | 27.00
(1 row)

Session 2 waited the same way, but ON CONFLICT is built for exactly this: once session 1 committed, it found the conflict and ran its DO UPDATE branch, so G7 ended at 27.00 with no error. The two statements each ran three times here with the same result. Use ON CONFLICT for single-row upserts that can run concurrently, and MERGE for batch syncs with several kinds of change, ideally from one process at a time.

Practice

Start each task from a fresh setup.sql. The examples above changed the tables, so the shown output assumes the starting data.

1. Add a branch so a matched product whose price didn’t change is left alone. Insert an unchanged C3 row into the feed first, and check that it doesn’t count as a change.

-- p1.sql
INSERT INTO feed VALUES ('C3', 'Rug', 55.00, false);   -- unchanged
MERGE INTO products p
USING feed f ON p.sku = f.sku
WHEN MATCHED AND f.discontinued THEN
    DELETE
WHEN MATCHED AND p.price = f.price THEN
    DO NOTHING
WHEN MATCHED THEN
    UPDATE SET price = f.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
RETURNING merge_action(), p.sku;

$ psql -d sqldemo -f p1.sql
INSERT 0 1
 merge_action | sku 
--------------+-----
 UPDATE       | A1
 DELETE       | B2
 INSERT       | E5
(3 rows)

MERGE 3

2. Make the products table mirror the feed exactly: update matches, insert new SKUs, delete discontinued ones, and delete anything the feed doesn’t list.

-- p2.sql
MERGE INTO products p
USING feed f ON p.sku = f.sku
WHEN MATCHED AND f.discontinued THEN DELETE
WHEN MATCHED THEN UPDATE SET price = f.price
WHEN NOT MATCHED BY TARGET THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT sku, price FROM products ORDER BY sku;

$ psql -d sqldemo -f p2.sql
MERGE 5
 sku | price 
-----+-------
 A1  |  9.50
 E5  | 75.00
(2 rows)

3. Before running a MERGE, find the SKUs that appear more than once in the feed, so you can fix them before the “cannot affect row a second time” error.

-- p3.sql
INSERT INTO feed VALUES ('A1', 'Mug', 9.00, false), ('E5', 'Chair', 70.00, false);
SELECT sku, count(*) AS copies
FROM feed
GROUP BY sku
HAVING count(*) > 1
ORDER BY sku;

$ psql -d sqldemo -f p3.sql
INSERT 0 2
 sku | copies 
-----+--------
 A1  |      2
 E5  |      2
(2 rows)