Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

An upsert fails because one INSERT would change the same row twice

ErrorERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The 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. 3The 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. 4ERROR: MERGE command cannot affect row a second timeMERGE joins one target row to several source rowsFix

What does the error look like?

  • 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.
  • ERROR: MERGE command cannot affect row a second time
  • HINT: 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.