How to Fix the PostgreSQL UPSERT Error: 'cannot affect row a second time'

The Problem Context

You’re likely syncing a large dataset—perhaps a 50MB CSV file or a JSON payload from an external API—into a target table. Everything looks solid in your code, but the moment you run a bulk UPSERT, PostgreSQL kills the transaction. This happens because your input data contains multiple entries for the same unique key (like a Primary Key or a email address).

The database engine demands predictability. It refuses to guess which set of values should "win" if your command tries to update the same physical row twice in one go. To maintain data integrity, PostgreSQL stops the process entirely rather than making an arbitrary choice.

The Error Message

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.

Why This Happens

The root cause is duplicate keys within your source data. PostgreSQL processes an INSERT statement as a single atomic operation. If your batch contains two rows with the same unique ID, the engine hits a logic loop:

• It attempts to insert the first row (or updates it if it exists).
• It then tries to update that same row again when it hits the second instance in the same batch.

PostgreSQL explicitly forbids this. It doesn't know if you want the first value, the last value, or a combination of both. Instead of risking a data collision, it throws an error.

A Real-World Example

Imagine an e-commerce table called product_inventory with a unique constraint on sku. You receive a batch update that looks like this:

INSERT INTO product_inventory (sku, stock_count)
VALUES 
  ('LAPTOP-001', 50), 
  ('PHONE-002', 120), 
  ('LAPTOP-001', 45) -- Duplicate SKU in the same batch!
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Because LAPTOP-001 appears twice, the database doesn't know if the final stock should be 50 or 45. The transaction fails immediately.

Step-by-Step Solutions

To fix this, you must clean your data before it reaches the INSERT statement. Here are the three most reliable ways to handle it.

Method 1: Using DISTINCT ON (Fast and Simple)

This is the best choice if you just need to pick one record from the duplicates and move on. Use DISTINCT ON in a subquery to filter the input.

INSERT INTO product_inventory (sku, stock_count)
SELECT DISTINCT ON (sku) sku, stock_count
FROM (
    VALUES 
      ('LAPTOP-001', 50), 
      ('PHONE-002', 120), 
      ('LAPTOP-001', 45)
) AS input_data(sku, stock_count)
ORDER BY sku, stock_count DESC -- Pick the highest stock count if duplicates exist
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Method 2: Using a CTE for Latest-Record Logic

If your data includes a timestamp, you likely want to keep the most recent update. A Common Table Expression (CTE) using ROW_NUMBER() is the most professional way to handle this for 1,000+ row batches.

WITH cleaned_updates AS (
    SELECT 
        sku, 
        stock_count, 
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY updated_at DESC) as rank
    FROM (
        VALUES 
          ('LAPTOP-001', 50, '2023-10-25 10:00:00'::timestamp), 
          ('PHONE-002', 120, '2023-10-25 10:05:00'::timestamp), 
          ('LAPTOP-001', 45, '2023-10-25 10:10:00'::timestamp)
    ) AS raw_data(sku, stock_count, updated_at)
)
INSERT INTO product_inventory (sku, stock_count)
SELECT sku, stock_count
FROM cleaned_updates
WHERE rank = 1
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Method 3: Pre-aggregating Values

For financial data or hit counters where you need to sum values rather than overwrite them, aggregate the data first.

INSERT INTO daily_sales (product_id, total_sold)
SELECT product_id, SUM(amount)
FROM (VALUES (101, 1), (101, 2), (102, 5)) AS batch(product_id, amount)
GROUP BY product_id
ON CONFLICT (product_id) 
DO UPDATE SET total_sold = daily_sales.total_sold + EXCLUDED.total_sold;

How to Verify the Fix

Don't just run the query and hope for the best. Follow these three steps to ensure your data is clean:

• Isolate the SELECT: Run only the subquery or CTE part of your refactored code. Ensure the row count matches the number of unique keys you expect.
• Check for Data Loss: If you started with 1,000 rows and ended with 800, verify that the 200 "missing" rows were indeed duplicates.
• Test Edge Cases: Try a small batch with three identical keys. If your ORDER BY logic is correct, the database should consistently pick the same record every time.

Key Takeaways

• One Statement, One Row: PostgreSQL views a single command as a single unit of work. It cannot modify the same row twice within that unit without breaking consistency.
• Sanitize Your Source: Never trust external data. Always assume a CSV or API payload contains duplicates and wrap your UPSERT in a deduplication layer.
• Be Explicit: Use ORDER BY when deduplicating. This ensures your UPSERT behavior is predictable and doesn't change randomly between runs.
• Match Your Targets: The columns in your ON CONFLICT clause must match the columns in your DISTINCT ON or PARTITION BY clause for the fix to work.