Postgres upsert errors: choose one row per conflict key

To fix a Postgres bulk upsert cardinality error, inspect the input to the failing statement and ensure it proposes one row per conflict key. Choose which value should survive before changing the SQL. Deduplication, snapshot replacement and event aggregation solve different data problems. A retry of unchanged ambiguous input does not resolve that decision. ^[1][2]

What does the error tell you about the input?#

Look for repeated keys inside the one INSERT that failed. PostgreSQL documents ON CONFLICT DO UPDATE as deterministic: one command cannot repeatedly affect the same existing row. Two proposed rows can collide through the same arbiter key even when the target table has no duplicate keys. The uniqueness constraint can be working exactly as intended. ^[1]

Record the SQLSTATE as well as the message. PostgreSQL assigns 21000 to cardinality violation; a deadlock has a different code and needs a different investigation. At the application boundary, capture a batch identifier, source identities and the generated statement shape. Avoid copying private payloads into logs just to count repeated keys. ^[5]

Scenario: an illustrative importer receives price snapshots for each tenant and SKU. Each snapshot has a monotonically increasing source sequence. Within that source contract, a later sequence supersedes an earlier one. The example uses PostgreSQL 18 syntax and a temporary schema that can be rehearsed in a disposable database. Prices, IDs and expected results below are synthetic; they describe a test case, not a client incident.

CREATE TEMP TABLE price_snapshot (
  tenant_id text NOT NULL,
  sku text NOT NULL,
  source_seq bigint NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (tenant_id, sku)
);
CREATE TEMP TABLE incoming_price (
  ingest_id bigint PRIMARY KEY,
  tenant_id text NOT NULL,
  sku text NOT NULL,
  source_seq bigint NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0)
);
INSERT INTO incoming_price VALUES
  (1, 'north', 'A', 10, 1200),
  (2, 'north', 'A', 11, 1300),
  (3, 'south', 'A', 7, 900);

-- Expected to fail: north/A is proposed twice.
INSERT INTO price_snapshot
SELECT tenant_id, sku, source_seq, price_cents
FROM incoming_price
ON CONFLICT (tenant_id, sku) DO UPDATE
SET source_seq = EXCLUDED.source_seq,
    price_cents = EXCLUDED.price_cents;

Here the identity is the pair tenant_id and sku. Deduplicating on SKU alone would collapse two tenants into one candidate. A primary key enforces uniqueness across its listed columns. In another schema, inspect expressions, collations, nullable columns and normalization triggers before assuming that a JavaScript string comparison reproduces the database identity. ^[3][1]

Where should you count duplicate keys?#

Count duplicates after every join and transformation that feeds INSERT. Inspecting the original file is insufficient if a join expands one source row into several. In the fixture, the final input is simply incoming_price. For a real importer, replace that relation with the exact SELECT that supplies the insert.

SELECT tenant_id, sku, count(*) AS proposed_rows,
       min(source_seq) AS first_version,
       max(source_seq) AS last_version
FROM incoming_price
GROUP BY tenant_id, sku
HAVING count(*) > 1;

-- Preflight: the same version must have one price.
SELECT tenant_id, sku, source_seq
FROM incoming_price
GROUP BY tenant_id, sku, source_seq
HAVING count(DISTINCT price_cents) > 1;

The first query should identify north/A with two proposed rows. The second should be empty for this fixture. Make a nonempty second result a stop condition: preserve or quarantine the batch and obtain a source decision before merging it. Merely displaying that result and continuing with the write would defeat the check. Keep the staged batch immutable between preflight and merge.

SELECT DISTINCT across all columns does not solve this example: source_seq and price_cents differ, so both rows survive. DISTINCT ON can select a row per key, but its ordering has to express the intended winner. A row-number calculation gives us another inspectable way to rank versions while retaining the original rows for diagnosis. ^[2][4]

Which duplicate should survive?#

For these snapshots, choose the highest source sequence. Arrival order is not the version contract: a delayed older delivery must not replace a newer price. The ingest ID below only orders identical repeats of a version after the ambiguity gate has passed. It does not authorize choosing between contradictory values at the same version.

Scenario: A replacement price and a balance adjustment need different reducers. If 1200 and 1300 represent replacement prices, adding them invents a price. If they represent two independent balance adjustments, selecting just one loses an adjustment. A syntactically valid GROUP BY is not a substitute for this decision. Write the meaning into the importer contract and review it with the owner of the source data.

How can the merge handle duplicates and older replays?#

Rank within the complete key, keep one candidate, then compare its source sequence with stored state. The ranking solves within-batch multiplicity. The conflict WHERE clause solves an older batch arriving after a newer batch. These are separate boundaries. PostgreSQL evaluates that condition after identifying a conflict; it is not a general-purpose input deduplicator. ^[4][1]

-- Run only after ambiguity preflight passed on this immutable batch.
WITH ranked AS (
  SELECT tenant_id, sku, source_seq, price_cents,
         row_number() OVER (
           PARTITION BY tenant_id, sku
           ORDER BY source_seq DESC, ingest_id DESC
         ) AS candidate_rank
  FROM incoming_price
)
INSERT INTO price_snapshot AS stored
  (tenant_id, sku, source_seq, price_cents)
SELECT tenant_id, sku, source_seq, price_cents
FROM ranked
WHERE candidate_rank = 1
ON CONFLICT (tenant_id, sku) DO UPDATE
SET source_seq = EXCLUDED.source_seq,
    price_cents = EXCLUDED.price_cents
WHERE EXCLUDED.source_seq > stored.source_seq
RETURNING tenant_id, sku, source_seq, price_cents;

Scenario: Expected final state: north/A is sequence 11 at 1300 cents; south/A remains a separate record at sequence 7 and 900 cents. Replaying these snapshots should leave that state unchanged. Replaying only north/A sequence 10 should also leave it unchanged. These are state assertions to verify, not a benchmark or a guarantee that every import workload has these semantics.

There is another ambiguity boundary across batches. Before merging, join incoming records to stored records on the full key and equal source_seq, then reject unequal payloads. Otherwise the greater-than guard silently retains the stored value when the same version arrives with a different price. Our tiny fixture stores only one payload field; a real comparison must include every authoritative field or a defined canonical payload representation.

SELECT i.tenant_id, i.sku, i.source_seq
FROM incoming_price AS i
JOIN price_snapshot AS s
  ON s.tenant_id = i.tenant_id AND s.sku = i.sku
 AND s.source_seq = i.source_seq
WHERE i.price_cents IS DISTINCT FROM s.price_cents;

SELECT tenant_id, sku, source_seq, price_cents
FROM price_snapshot
ORDER BY tenant_id, sku;

A preflight query followed by a write needs a concurrency contract too. In this illustrative design, one importer owns a tenant’s snapshot stream and the staged batch cannot change. With concurrent writers, serialize that stream or design a transaction-level conflict protocol and test it under the actual isolation level. The example deliberately does not claim that a separate read closes every race.

What should the regression check prove?#

Check the stored price after a replay, and check that contradictory versions stop before the write. A CTE makes the reduction steps easier to inspect, but naming those steps does not prove their business meaning. Save the raw staged input and its batch ID so a failed decision can be reconstructed. ^[6]

• A clean batch creates separate north/A and south/A records with the expected versions and prices.
• Scenario check: Two versions of north/A in either arrival order select sequence 11.
• Scenario check: Identical repeats of sequence 11 produce the same state.
• Different prices at the same source version stop the batch before merge, including disagreements with stored state.
• An older replay preserves the newer stored snapshot, while a genuinely newer sequence advances it.
• A repeated delivery of additive events is tested under an event-ID policy rather than reusing snapshot selection.

Keep returned-row counts separate from accepted-input counts. A conflict condition that skips an older row can return no row for that candidate. Report input rows, rejected keys, chosen candidates and actual state changes as different quantities; otherwise a no-change replay can look like missing data. ^[1]

Repeated execution has a different purpose when a transaction loses a deadlock. The Postgres deadlock guide follows that retry boundary. If imports emit downstream work after committing, the transactional outbox guide follows the delivery boundary. Neither replaces a decision about duplicate input.

Questions about bulk upserts#

Can two separate upserts update the same key?

Yes. The deterministic-statement restriction concerns repeated effects within one command. A later separate command may update the same stored key according to its conflict action. If an apparently single-row operation raises this error, inspect the generated SQL, source SELECT and triggers rather than inferring the query shape from an ORM method name. ^[1]

Will reducing the batch size fix the importer?

It can hide the collision by separating repeated keys into different statements, but it does not define the winner. The final state may then depend on delivery order. Keep batching as a performance and recovery choice after the snapshot or event contract has been made explicit.

Should I switch to ON CONFLICT DO NOTHING?

Use it when ignoring an existing identity is the intended behavior. It avoids a conflicting insertion; it does not apply a newer replacement snapshot. Changing from update to ignore merely to remove an error can preserve outdated state. Decide what an existing key means before changing the conflict action. ^[1]

Can timestamps replace the source sequence?

Only if the source contract makes them a reliable order and defines ties. Equal timestamps can carry different payloads, and local arrival time measures delivery rather than source freshness. Keep a stable tie policy and an ambiguity check; do not assume a sort makes an unreliable version trustworthy.

Does this SQL cover every unique index?

No. The fixture has a non-null composite primary key with ordinary text identity. Expression indexes, partial predicates, collations, nullable keys and BEFORE INSERT normalization can change which inputs conflict. Match the reduction to the actual arbiter behavior and verify that behavior on the production schema in a safe test environment. ^[1][3]