ON CONFLICT DO UPDATE command cannot affect row a second time

What it means

An INSERT … ON CONFLICT … DO UPDATE statement proposed two rows that collide on the conflict key (the same email, the same (email, list) pair). The first would insert or update a row; the second would then update that same row again, in the same statement. PostgreSQL refuses, because the result would depend on the order the rows happened to be processed in. The whole statement fails, and none of its rows are written.

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.

The duplicates are inside your batch, not between the batch and the table: a clash with a row that was already in the table is exactly what DO UPDATE handles.

MERGE has the same rule, worded MERGE command cannot affect row a second time, when two source rows match one target row. ON CONFLICT DO NOTHING doesn’t raise it: it keeps the first row and skips the rest.

Common causes

1. A batch built from data with repeats: a CSV, an API page or an event stream with the same record twice.
2. Keys that are equal only after normalising: with a unique index on lower(email), Ada@example.com and ada@example.com collide.
3. An INSERT … SELECT from a join that multiplies rows, so the same key comes out once per matching row.
4. Batching layers (ORMs, bulk-upsert helpers) that collect changes to the same record into one statement.

How to fix it

Find the repeated keys

If the batch comes from a staging table or a query:

SELECT email, list, count(*)
FROM staging_subscribers
GROUP BY email, list
HAVING count(*) > 1;

Keep one row per key

Decide which version wins, then keep only that one. DISTINCT ON keeps the first row per key in the ORDER BY order, here the latest by seq:

INSERT INTO subscribers (email, list, name)
SELECT DISTINCT ON (email, list) email, list, name
FROM staging_subscribers
ORDER BY email, list, seq DESC
ON CONFLICT (email, list) DO UPDATE SET name = excluded.name;

If the duplicates should add up instead (stock movements, counters), aggregate first:

INSERT INTO stock (sku, qty)
SELECT sku, sum(qty) FROM stock_movements GROUP BY sku
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + excluded.qty;

For an expression index, deduplicate on the same expression: DISTINCT ON (lower(email), list).

Deduplicate in the application

When you build the VALUES list in code, key a map by the conflict columns before sending, so the last (or first) version of each record is the only one in the statement.

Or upsert one row at a time

Separate statements can each update the same row, in order. It’s slower for big batches, but it gives “last write wins” without extra logic.

Reproduce it

On PostgreSQL 18.6, with a unique constraint on (email, list):

INSERT INTO subscribers (email, list, name)
VALUES ('grace@example.com', 'news', 'Grace'),
       ('grace@example.com', 'news', 'Grace H')
ON CONFLICT (email, list) DO UPDATE SET name = excluded.name;

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.

With \set VERBOSITY verbose, psql shows the code: ERROR: 21000: ON CONFLICT DO UPDATE command cannot affect row a second time. The same rows through SELECT DISTINCT ON (email, list) … ORDER BY email, list, seq DESC inserted one row, named Grace H. The same two rows with ON CONFLICT … DO NOTHING inserted the first and skipped the second (INSERT 0 1). A MERGE with two source rows for one existing target row gave:

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

PostgreSQL 14.24 gives the same message for the upsert (MERGE arrived in PostgreSQL 15).

In Inlet

CSV import loads a file into a table, so you can stage a batch and check it for repeated keys with a GROUP BY … HAVING count(*) > 1 before upserting. When a statement fails, Inlet shows the server’s error with PostgreSQL’s hint; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.