# INSERT ... ON CONFLICT vs MERGE in Postgres: which upsert to use, and the errors each one throws When to upsert with INSERT ... ON CONFLICT and when to use MERGE in PostgreSQL 17, why MERGE can fail with a duplicate key error under concurrency, and how to fix 'cannot affect row a second time', empty RETURNING and 'no unique or exclusion constraint matching the ON CONFLICT specification'. Every statement reproduced on Postgres 17.11. **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.** ## The short answer Use `INSERT ... ON CONFLICT` when concurrent writers may insert the same key: a request handler, a webhook consumer, a counter. The [Postgres documentation](https://www.postgresql.org/docs/17/sql-insert.html) 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. | You hit | Statement | Why | Fix | | ----------------------------------------------------------------------------------- | ------------------------ | --------------------------------------------------------------------------- | ------------------------------------------------------------- | | `duplicate key value violates unique constraint` | `MERGE` | Two sessions insert the same new key; `MERGE` doesn't fall back to `UPDATE` | Use `ON CONFLICT` for that path, or retry on SQLSTATE `23505` | | `ON CONFLICT DO UPDATE command cannot affect row a second time` | `INSERT ... ON CONFLICT` | The batch contains the same key twice | Deduplicate the batch with `DISTINCT ON` | | `MERGE command cannot affect row a second time` | `MERGE` | Two source rows match one target row | Deduplicate the source | | `RETURNING` comes back empty | Both | Only rows actually inserted or updated are returned | Read the existing row in the same statement | | `there is no unique or exclusion constraint matching the ON CONFLICT specification` | `INSERT ... ON CONFLICT` | The conflict target doesn't match a unique index | Add the index, or repeat the partial index's `WHERE` | | Rows rewritten when nothing changed | Both | An update writes a new row version even with equal values | Add `WHERE ... IS DISTINCT FROM` | | Gaps in your ids | `INSERT ... ON CONFLICT` | The id is drawn before the conflict is detected | Expect gaps; don't use ids as a count | ## The setup Any Postgres 17 works. With Docker: ```bash 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`: ```sql 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: ```sql 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; ``` ```text 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: ```text 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. ## 1. Two sessions, one new key: `MERGE` fails, `ON CONFLICT` doesn't 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: ```sql -- 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: ```sql -- 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: ```text ERROR: duplicate key value violates unique constraint "products_sku_key" DETAIL: Key (sku)=(WC-01) already exists. ``` ![Two timelines. With MERGE, session B finds no row, tries to insert, waits for session A, and fails with a duplicate key error when A commits. With INSERT ... ON CONFLICT, session B waits the same way and then updates the row A inserted.](/blog/assets/fdd5bd86670b0486.svg) 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](https://www.postgresql.org/docs/17/sql-merge.html) 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: ```sql -- 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; ``` ```sql -- 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: ```sql SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku; ``` ```text 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`. ## 2. "cannot affect row a second time" A batch upsert that contains the same key twice fails as a whole: ```sql 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; ``` ```text 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: ```sql 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; ``` ```text 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: ```sql 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); ``` ```text 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. ## 3. `RETURNING` comes back empty `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: ```sql INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00) ON CONFLICT (sku) DO NOTHING RETURNING id; ``` ```text 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: ```sql 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; ``` ```text 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. ## 4. "there is no unique or exclusion constraint matching the ON CONFLICT specification" `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: ```sql INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00) ON CONFLICT (name) DO NOTHING; ``` ```text 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: ```sql 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; ``` ```text 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: ```sql INSERT INTO accounts (email) VALUES ('ana@example.com') ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING; ``` ```text 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. ## 5. Upserts that rewrite rows that didn't change `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): ```sql 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'; ``` ```text 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: ```sql 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; ``` ```text 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. ## 6. Gaps in your ids After everything above, the table holds four products: ```sql SELECT id, sku FROM products ORDER BY id; ``` ```text 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](https://www.postgresql.org/docs/17/functions-sequence.html) 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. ## What `MERGE` does that `ON CONFLICT` can't 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: ```sql 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: ```sql 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; ``` ```text 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](https://www.postgresql.org/docs/17/release-17.html): `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`. ## Which one to use 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. ## Rehearse the sync on a copy of your data 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](/auth/signin?ref=blog-postgres-upsert-on-conflict-vs-merge)