One of our nightly syncs copies packaging inspection results from a spreadsheet into PostgreSQL. One morning it stopped with this error:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
SQLSTATE: 21000What the error means
The sync writes rows in batches with an upsert: insert each row, or update it if a row with the same key already exists. PostgreSQL refuses when a single batch contains two rows with the same key, because it cannot decide which one should win. The code 21000 is its "cardinality violation" error.
In our case the key was a reference copied from the source sheet. The sheet had two legitimate entries that shared a reference, so both landed in the same batch.
Two ways to fix it
If the rows are true duplicates, keep one
Remove duplicates before the upsert, keeping the last occurrence. This is right when the second row is a correction of the first.
If the rows are different records, make the key unique
Ours were different records, so we added an occurrence counter. The first row with a reference keeps it, and later rows get a suffix.
const seen = new Map();
for (const row of rows) {
const n = (seen.get(row.source_ref) || 0) + 1;
seen.set(row.source_ref, n);
row.source_key = n === 1 ? row.source_ref : `${row.source_ref}#${n}`;
}The upsert then uses source_key as its conflict target. One caution: the counter depends on row order, so if rows in the source can be reordered, build the key from more columns or from a hash of the row's content instead.
The lesson
Source references are rarely as unique as they look. We now apply the same check to every sync we build, before the first run rather than after the first failure.
Field notes like this come from building QualityPulse and client systems. Read more field notes.