INSERT ... ON CONFLICT vs MERGE in Postgres: which upsert to use, and the errors each one throws

Postgres has two ways to say "insert this row, or update it if it's already there": INSERT ... ON CONFLICT, around since 9.5, and MERGE, added in 15 and made much more useful in 17 with RETURNING, merge_action() and WHEN NOT MATCHED BY SOURCE. They look interchangeable and they are not. Two sessions upserting the same new key will both succeed with ON CONFLICT; with MERGE, one of them gets a duplicate key error. This post reproduces that race and the five other errors and surprises people hit with upserts, shows the fix for each, and ends with a rule for picking one. Every statement and every output below comes from a local PostgreSQL 17.11, the major version Relyt One runs.

Use INSERT ... ON CONFLICT when concurrent writers may insert the same key: a request handler, a webhook consumer, a counter. The Postgres documentation guarantees "an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency". Use MERGE when you reconcile a whole set of rows against a table, such as a nightly feed or a staging table, and you want conditional updates, deletes, or a report of what happened to each row.

Any Postgres 17 works. With Docker:

docker run -d --name upsert-lab -e POSTGRES_PASSWORD=secret postgres:17.11-alpine
docker exec -it upsert-lab psql -U postgres

One table, keyed by an identity column, with a unique sku:

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   text NOT NULL UNIQUE,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);

The basic upsert names the conflicting column, and EXCLUDED is the row you tried to insert:

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 49.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING id, sku, price, (xmax = 0) AS inserted;

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 49.00 | t

Run it again with 45.00 and the same row comes back updated, with inserted false:

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 45.00 | f

The (xmax = 0) test is a widely copied trick for telling an insert from an update. It works because a freshly inserted row version has no xmax, but it relies on how Postgres stores row versions, not on documented behavior. MERGE has a documented way to report the action, shown at the end of this post.

Open two psql sessions. In the first, start a transaction and upsert a SKU that doesn't exist yet with MERGE, but don't commit:

-- Session A
BEGIN;
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 59.00)) AS f(sku, name, price)
ON p.sku = f.sku
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);

In the second session, run the same MERGE for the same SKU. It hangs:

-- Session B
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 55.00)) AS f(sku, name, price)
ON p.sku = f.sku
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);

Type COMMIT; in session A. Session B wakes up with:

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(WC-01) already exists.

Here is what happened. When session B started, A's row wasn't committed, so B's join found no match and B chose the WHEN NOT MATCHED branch. Its insert then waited on the unique index for A to finish. Once A committed, the key existed, and MERGE has no way to switch to its WHEN MATCHED branch at that point. The MERGE documentation says as much: it points you to INSERT ... ON CONFLICT "as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs."

Now the same race with ON CONFLICT and another new SKU:

-- Session A
BEGIN;
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 79.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

-- Session B: waits for A
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 75.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

After COMMIT; in session A, session B prints INSERT 0 1, and the row holds B's price:

SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku;

  sku  | price
-------+-------
 HS-01 | 75.00
 WC-01 | 59.00

WC-01 kept session A's price because session B's MERGE failed. If you use MERGE on a path where two writers can create the same key, catch SQLSTATE 23505 and retry the whole transaction. At the default READ COMMITTED isolation level, the retry takes a new snapshot, sees A's row and runs the WHEN MATCHED branch. Retrying only the statement doesn't work inside an explicit transaction: the error has already aborted it, unless you wrapped the MERGE in a savepoint and roll back to it. Or keep that path on ON CONFLICT.

A batch upsert that contains the same key twice fails as a whole:

INSERT INTO products (sku, name, price)
VALUES ('MN-27', 'Monitor', 199.00),
       ('MN-27', 'Monitor 27"', 189.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

This is deliberate. Postgres won't guess which of the two rows should win, so it refuses to apply both. It happens with batches built from event streams or CSV files where the same key shows up more than once. Deduplicate before the insert and decide which row wins. Here the later row in the batch wins:

INSERT INTO products (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM (VALUES ('MN-27', 'Monitor', 199.00, 1),
             ('MN-27', 'Monitor 27"', 189.00, 2)) AS batch(sku, name, price, seq)
ORDER BY sku, seq DESC
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING sku, name, price;

  sku  |    name     | price
-------+-------------+--------
 MN-27 | Monitor 27" | 189.00

MERGE has the same rule with a different message. Two source rows that match one existing target row:

MERGE INTO products AS p
USING (VALUES ('KB-01', 'Keyboard', 44.00),
              ('KB-01', 'Keyboard', 43.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

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

If the duplicated key doesn't exist in the table yet, MERGE takes the WHEN NOT MATCHED branch twice and the second insert fails with a duplicate key error instead. The fix is the same in both cases: one source row per key.

DO NOTHING is the natural way to write "create it if it's missing", and RETURNING id the natural way to get the id. For a row that already exists, you get nothing:

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO NOTHING
RETURNING id;

 id
----
(0 rows)

RETURNING only returns rows that were actually inserted or updated. To always get an id back, read the existing row in the same statement:

WITH ins AS (
  INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
  ON CONFLICT (sku) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM products WHERE sku = 'KB-01'
LIMIT 1;

 id
----
  1

One gap remains. If another session inserted the same SKU and hasn't committed yet, the INSERT waits for it and does nothing, but the SELECT part uses the snapshot taken when the statement started, so it can't see the new row either, and you get zero rows. Treat an empty result as "try again". The alternative, DO UPDATE SET sku = EXCLUDED.sku, always returns the row but writes a new row version every time, which is the problem in section 5.

ON CONFLICT (column) doesn't mean "check this column for duplicates". It means "find the unique index on exactly these columns and use it". Name a column without one and the statement fails before it does anything:

INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00)
ON CONFLICT (name) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

The less obvious case is a partial unique index, the usual way to keep emails unique among accounts that aren't soft-deleted:

CREATE TABLE accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX accounts_live_email ON accounts (email) WHERE deleted_at IS NULL;

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

The index exists, but it only covers rows where deleted_at IS NULL, so Postgres won't use it unless you say the same thing in the conflict target:

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

INSERT 0 1

If your ORM generates the ON CONFLICT clause, check whether it can emit that WHERE. Don't swap the partial index for a regular unique index just to make the ORM happy: that changes the rule, and a soft-deleted account would then keep its email taken.

DO UPDATE updates the row whenever there is a conflict, even if every value is the same. In Postgres, an update writes a new row version, and the old one becomes a dead tuple that vacuum has to clean up. You can see it in xmin, the id of the transaction that wrote the current version (your numbers will differ):

SELECT xmin, price FROM products WHERE sku = 'KB-01';
INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;
SELECT xmin, price FROM products WHERE sku = 'KB-01';

 xmin | price
------+-------
  742 | 45.00

 xmin | price
------+-------
  753 | 45.00

Same price, new row version. For a sync job that re-sends a million unchanged rows every hour, that is a million dead tuples an hour, plus the WAL and any triggers that fire on update. Skip updates that change nothing:

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
WHERE (products.name, products.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price)
RETURNING id;

 id
----
(0 rows)

xmin stays at 753. Use IS DISTINCT FROM, not <>, so a change from or to NULL counts as a change. Note the empty RETURNING: a row that the WHERE filters out is locked but not updated, so it isn't returned, which is section 3 again.

After everything above, the table holds four products:

SELECT id, sku FROM products ORDER BY id;

 id |  sku
----+-------
  1 | KB-01
  3 | WC-01
  5 | HS-01
  9 | MN-27

INSERT ... ON CONFLICT builds the row it would insert, including the next identity value, before it checks for a conflict. Every upsert that ended up updating, every DO NOTHING that did nothing, and every insert attempt that failed used up an id. Statements that failed before trying to insert anything, like the ON CONFLICT (name) error in section 4, didn't. The sequence documentation calls out this exact case. A MERGE that hits WHEN MATCHED doesn't draw an id, but a failed MERGE insert does: id 4 went to session B in section 1.

This is harmless as long as nothing treats ids as a count. If a high-traffic upsert path burns through a 32-bit integer id faster than you planned, use bigint, as the table above does.

Everything so far makes MERGE look like the riskier choice. It is the better one for a different job: reconciling a table against a complete source. Say a supplier sends a full price list every night and you load it into a staging table:

CREATE TABLE price_feed (
  sku   text PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO price_feed VALUES
  ('KB-01', 'Keyboard',    42.00),
  ('MN-27', 'Monitor 27"', 189.00),
  ('HS-01', 'Headset',     79.00),
  ('MS-01', 'Mouse',       19.00);

One MERGE updates what changed, inserts what is new, deletes what the supplier dropped, and tells you which of the three happened to each row:

MERGE INTO products AS p
USING price_feed AS f
ON p.sku = f.sku
WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (f.name, f.price) THEN
  UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), p.sku, p.price;

 merge_action |  sku  | price
--------------+-------+-------
 UPDATE       | KB-01 | 42.00
 UPDATE       | HS-01 | 79.00
 INSERT       | MS-01 | 19.00
 DELETE       | WC-01 | 59.00

MN-27 is missing from the output because it didn't change. Three parts of this statement are new in Postgres 17: RETURNING on MERGE, the merge_action() function, and WHEN NOT MATCHED BY SOURCE. On Postgres 16, MERGE ... RETURNING is a syntax error. Before 17, the same sync took an upsert, a separate DELETE ... WHERE NOT EXISTS, and some bookkeeping to report what changed.

WHEN NOT MATCHED BY SOURCE THEN DELETE deletes every row in the table that isn't in the source. Point it at a partial feed, or an empty staging table after a failed load, and it deletes rows you meant to keep. Check the feed's row count before you run it, or add a condition on the target's columns, for example WHEN NOT MATCHED BY SOURCE AND p.supplier = 'acme' THEN DELETE.

The concurrency caveat from section 1 still applies. A nightly sync is usually the only writer for those keys, which is why MERGE fits it. If request traffic can insert the same SKUs while the sync runs, retry the sync on 23505.

1. Writers that may insert the same new key at the same time: INSERT ... ON CONFLICT DO UPDATE.
2. Reconciling a table with a complete source, with updates, inserts and deletes in one pass: MERGE, with a retry on SQLSTATE 23505 if anything else writes those keys.
3. Need to know, per row, whether it was inserted, updated or deleted: MERGE ... RETURNING merge_action() on Postgres 17.
4. Batches: deduplicate on the key first, for either statement.
5. Get-or-create: DO NOTHING plus a read of the existing row, and treat zero rows as "retry".
6. Re-sending unchanged data: add WHERE ... IS DISTINCT FROM to the update.
7. Partial unique indexes: repeat the index's WHERE in the conflict target.

The risky statement in this post is the one with DELETE in it. Before you point a new MERGE at production, run it on a copy of production. On Relyt One, every branch has its own compute on top of shared copy-on-write storage, so you can create a branch of your database, load tonight's feed into a staging table there, run the MERGE, and read the merge_action() output. If it plans to delete half the catalog, you find out on the branch, and you throw the branch away. Relyt One runs PostgreSQL 17, so RETURNING, merge_action() and WHEN NOT MATCHED BY SOURCE work there as shown above.

Start for free