The import finished, the log printed a success line, and most of the rows are not in the table. If that table has a partial unique index, one row probably took the whole batch down with it.
WHERE clause) and your duplicate check before each insert tests a narrower condition than the index does, some rows slip past the check and hit the index. One unique_violation aborts the transaction, so every other valid row in the batch rolls back too, and a tool that prints the count it meant to write will still report success. Fix it by writing the rows with INSERT … ON CONFLICT … DO UPDATE (an UPSERT) whose conflict target repeats the index’s predicate exactly. Then add one check that catches the whole family of bugs: after each batch, confirm rows_in == rows_committed + rows_skipped.Table of Contents
The symptom
You run an import. It reads a spreadsheet of contacts and writes them to a staging table, in one transaction, because a half-applied import is harder to clean up than none at all. The run ends without an error, or with a summary that looks like partial success. Then you count the rows and most of them are missing.
Nothing in the log points at the missing rows. The only error, if you can find it, names a row that looks perfectly fine. That mismatch is the clue.
Step 1: check whether the table has a partial unique index
A partial unique index enforces uniqueness only over the rows that match a predicate. A contact staging table might use one like this:
CREATE UNIQUE INDEX one_pending_per_email
ON contact_import (list_id, email)
WHERE status = 'pending';
Code language: SQL (Structured Query Language) (sql)In words: at most one pending row per email on each mailing list. Rows that were already approved or rejected don’t count, so the import history can grow freely while only work in progress has to be unique. That is a sensible design, and it is also where the trap starts.
In PostgreSQL you can list a table’s indexes and their predicates with \d contact_import in psql, or by querying pg_indexes. Look for UNIQUE together with WHERE.
Step 2: compare your duplicate check with the index
Most importers skip rows that are already present before they insert. Suppose yours was written for the common case of re-running the same file:
Skip the row if a pending row for this email already exists with the same phone number.
Read the two conditions side by side. The check skips a pending row with the same phone. Now someone fixes a phone number in the spreadsheet and re-imports it. The edited row has a pending twin with a different phone, so it passes the check, reaches a plain INSERT, and trips the index: unique_violation.
The check guards “same email, same phone”. The index forbids “same email, any phone”. The rows in between are the ones that blow up.
Step 3: understand why one error empties the batch
On its own, a single rejected row would be a small problem. Inside an all-or-nothing transaction it is a big one. A unique_violation on one row aborts the transaction, and every other row written in it rolls back, however valid it was.
In one case we diagnosed, a nine-row batch kept a single row. One row had a pending twin with different details; its insert raised; the transaction unwound; eight good rows were gone. The summary still read APPLIED: confirm=1, because the code around the insert reported the count it intended to write, not the count the database kept. Nobody noticed for a while, since the log said it worked.
Three things keep this hidden:
- The row that raises is not the row you lose. The conflicting row is often the newer, corrected record. The casualties are unrelated rows that shared its transaction, so reading the error sends you to the wrong place.
- The summary lies by omission. Later steps run on whatever was committed and report their own neat counts. Taken together, the output reads like success.
- A partial index invites the wrong mental model. “An approved row can’t conflict” is true, and it makes the space of possible conflicts look smaller than it is.
The row that raises is usually fine; the rows you lose are the bystanders in its transaction.
Step 4: replace the plain INSERT with an UPSERT on the same predicate
Stop issuing an INSERT that can only raise. Use INSERT … ON CONFLICT … DO UPDATE so the clash is resolved in place, and give it the same predicate as the partial index:
INSERT INTO contact_import (list_id, email, phone, status)
VALUES (:list_id, :email, :phone, 'pending')
ON CONFLICT (list_id, email) WHERE status = 'pending'
DO UPDATE SET phone = EXCLUDED.phone, updated_at = now()
RETURNING (xmax = 0) AS was_insert;
Code language: SQL (Structured Query Language) (sql)Two details matter here:
WHERE status = 'pending'on the conflict target has to match the index’s predicate. Without it, PostgreSQL cannot pick that index to arbitrate the conflict and the statement fails.RETURNING (xmax = 0)is true for a fresh insert and false for an update. Count both, so the import can report “8 inserted, 1 updated” instead of hiding the update.
Before you adopt this, decide whether overwriting is acceptable. It works here because the staging table holds proposals waiting for review, and a newer proposal should replace an older one. If each colliding row were a real record in its own right (two genuine customers who happen to share a key), DO UPDATE would quietly destroy one of them. In that case, change the key so the rows don’t collide, or send the conflict to a person. It is the same question as failing before an irreversible side effect: decide what must not be destroyed before you write.
Step 5: reconcile the counts after every batch
The UPSERT fixes this particular bug. A counting rule catches the whole class:
After any batch write, assert
rows_in == rows_committed + rows_intentionally_skipped.
The database knows exactly how many rows it kept. Compare that with the number you read from the file, and a silent rollback turns into a loud mismatch. If the counts don’t add up, fail the run rather than print a summary. Verifying the end state against the input, instead of trusting the writer’s own success line, is good practice for any write that moves data between systems, and it pairs well with a sane setup for database connection limits and concurrency.
Checklist
- Does the target table have a unique index with a
WHEREclause? - Does your skip-if-present check test exactly what that index enforces, no more and no less?
- Is the batch written in one transaction, so a single error rolls back everything?
- Do the writes use
ON CONFLICT (…) WHERE <index predicate>instead of a plainINSERT? - Is overwriting a clashing row actually correct for this table?
- Does the import compare rows read with rows committed plus rows skipped, and fail on a mismatch?
Frequently Asked Questions
Why does ON CONFLICT fail with “no unique or exclusion constraint matching” on a partial index?
Because the conflict target has to repeat the index’s predicate. For a partial unique index, ON CONFLICT (list_id, email) on its own matches no index, so PostgreSQL raises an error instead of resolving the clash. Write ON CONFLICT (list_id, email) WHERE status = 'pending', with the same columns and the same WHERE clause as the index, and PostgreSQL can use it to arbitrate.
Can I use ON CONFLICT DO NOTHING instead of DO UPDATE?
Yes, and it stops the rollback just as well: the clashing row is skipped and the rest of the batch commits. The cost is that the newer data in that row is thrown away without a word. In the phone-number example, the corrected number never lands. Use DO NOTHING only when the existing row should win, and count the skipped rows so the reconciliation check still adds up.
Should I use savepoints instead, so one bad row doesn’t roll back the rest?
You can wrap each row in a savepoint and roll back only that row, but then the batch is no longer all-or-nothing, and you still need to report the rejected rows somewhere. If the conflict is one you can resolve automatically, the UPSERT is simpler. If it needs a person, collect the conflicts and fail the batch with a clear list.
