An upsert fails because one INSERT would change the same row twice
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second timeShort answer
PostgreSQL does not allow one INSERT ... ON CONFLICT DO UPDATE to change the same row twice. Two of the rows you send carry the same value in the columns the conflict index covers. Find the repeated keys in your input, keep one row per key with DISTINCT ON, then run the upsert again. MERGE follows the same rule.
Fix it fast
1Find out which cause you have
List every key that appears more than once in the data you are about to insert. Swap staging_prices and sku for your own source and conflict column. If this returns rows, the error is explained.
SELECT sku, count(*) FROM staging_prices GROUP BY sku HAVING count(*) > 1;2Match what you see, then jump to its fix
- 1
The query above returns rows, and the INSERT lists several rows with the same value in the conflict columnThe input holds the same key more than onceFix - 2
The staging table has no repeated key, but the INSERT reads from a join and the error appearsA join in the SELECT multiplies the rowsFix - 3
The raw values differ (for example Ann@example.com and ann@example.com) but a unique index on lower(email) existsThe conflict target is an expression, and two rows are equal only after it is appliedFix - 4
ERROR: MERGE command cannot affect row a second timeMERGE joins one target row to several source rowsFix
- 1
What does the error look like?
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second timeHINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.ERROR: MERGE command cannot affect row a second timeHINT: Ensure that not more than one source row matches any one target row.
Cause 1 of 4
The input holds the same key more than once
1Check
An upsert is a deterministic statement: it may not affect any single existing row more than once, so rows proposed for insertion must not duplicate each other in the columns an arbiter index or constraint covers. Print the repeated rows side by side to decide which one should win.
SELECT *
FROM staging_prices
WHERE sku IN (SELECT sku FROM staging_prices GROUP BY sku HAVING count(*) > 1)
ORDER BY sku, updated_at;2Fix
Collapse the input to one row per key before it reaches ON CONFLICT. DISTINCT ON keeps the first row of each key, and the ORDER BY decides which row that is. Without an ORDER BY the surviving row is unpredictable.
INSERT INTO prices (sku, price, updated_at)
SELECT DISTINCT ON (sku) sku, price, updated_at
FROM staging_prices
ORDER BY sku, updated_at DESC
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price, updated_at = EXCLUDED.updated_at;3Verify
The statement runs without the error, and the target holds one row for each distinct key you sent.
SELECT count(*), count(DISTINCT sku) FROM prices;Confirmed by postgresql.org
Cause 2 of 4
A join in the SELECT multiplies the rows
1Check
Run only the SELECT part of your INSERT, with its joins, and group it by the conflict column. A join to a table with several matches per key returns that key several times.
SELECT s.sku, count(*)
FROM staging_prices s
JOIN price_tiers t ON t.sku = s.sku
GROUP BY s.sku
HAVING count(*) > 1;2Fix
Pick the row you want inside a subquery, so the outer INSERT sees each key once.
INSERT INTO prices (sku, price)
SELECT sku, price
FROM (
SELECT DISTINCT ON (s.sku) s.sku, t.price
FROM staging_prices s
JOIN price_tiers t ON t.sku = s.sku
ORDER BY s.sku, t.min_qty
) AS one_per_sku
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;3Verify
Run the INSERT again and confirm it finishes without the error.
SELECT count(*), count(DISTINCT sku) FROM prices;Confirmed by postgresql.org
Cause 3 of 4
The conflict target is an expression, and two rows are equal only after it is applied
1Check
ON CONFLICT can infer an arbiter index from an expression such as lower(email). Duplicates are judged on the indexed expression, not on the raw column, so group by the same expression.
SELECT lower(email), count(*)
FROM staging_users
GROUP BY lower(email)
HAVING count(*) > 1;2Fix
Apply the same expression in DISTINCT ON, and order by something that makes the winner clear.
INSERT INTO users (email, name)
SELECT DISTINCT ON (lower(email)) email, name
FROM staging_users
ORDER BY lower(email), created_at DESC
ON CONFLICT (lower(email)) DO UPDATE SET name = EXCLUDED.name;3Verify
The upsert completes, and the table has no two emails that match case-insensitively.
SELECT lower(email), count(*) FROM users GROUP BY lower(email) HAVING count(*) > 1;Confirmed by postgresql.org
Cause 4 of 4
MERGE joins one target row to several source rows
1Check
In MERGE, a target row should not join to more than one source row. If it does, only one candidate change is applied and the later attempt to modify the same row raises the error. Count matches per join key in the source.
SELECT sku, count(*)
FROM staging_prices
GROUP BY sku
HAVING count(*) > 1;2Fix
Feed MERGE a source that has one row per join key.
MERGE INTO prices AS t
USING (
SELECT DISTINCT ON (sku) sku, price
FROM staging_prices
ORDER BY sku, updated_at DESC
) AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, price) VALUES (s.sku, s.price);3Verify
The MERGE runs to the end without the error.
SELECT count(*), count(DISTINCT sku) FROM prices;Confirmed by postgresql.org
How do I stop it coming back?
- Remove duplicates in the SELECT that feeds the upsert, so every batch is clean no matter where it came from.
- Always write an ORDER BY next to DISTINCT ON. It states which row you want to win.
- Normalise keys (trim, lower-case) in the same expression your unique index uses, before you compare or insert them.
- Load raw files into a staging table that has the same unique constraint as the target, so a repeated key fails at load time, where the bad row is easy to find.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix