Once a unique constraint is the mechanism preventing duplicate rows, how should the write path be structured around it — catching the constraint-violation error, or using an insert-if-absent statement — and what changes for the surrounding transaction?

## Two strategies, chosen by what a duplicate means Once uniqueness lives in the database, every write path has to decide what to do when the constraint fires. There are two shapes, and picking the wrong one produces either ugly errors or hidden bugs. **Optimistic insert plus violation handling.** Attempt the insert; if the constraint rejects it, catch the error and turn it into a domain outcome. This is right when the duplicate is meaningful to the caller — registering an email that already exists, claiming a taken username, importing a record that conflicts with an existing one. The user gets a specific message and the flow branches. **Insert-if-absent (conflict-tolerant insert / upsert).** Express 'insert, and if it collides do nothing' or 'insert, and if it collides update' as a single atomic statement. This is right when the duplicate is expected and boring: a retried HTTP request, an at-least-once message consumer, a periodic catalogue sync, a cache-like table. No exception is raised, no error path is exercised, and the operation is naturally idempotent. A useful test: if a duplicate should produce a log line and a metric, catch the error. If it should produce nothing at all, use the conflict-tolerant statement. ## Getting the error handling right **Match structurally, not textually.** Every engine exposes a machine-readable error code for a unique violation, and exposes the name of the constraint that fired. Match on those. Parsing the human-readable message is fragile across versions and locales, and it is a classic source of production breakage after an upgrade. **Name your constraints.** A table with three unique keys produces three different user-facing messages. If the constraints carry auto-generated names, the application cannot tell them apart reliably, and the names may differ between environments or change when a table is rebuilt. An explicit naming convention makes error mapping a lookup table. **Do not swallow it.** Catching a unique violation and silently returning success is only correct in the idempotent case, and there the conflict-tolerant statement expresses the intent better. In the error case, an over-broad catch will one day hide a violation of a *different* constraint. ## What the failure does to the transaction This is the part that separates a working implementation from one that fails under load, and engines differ. In PostgreSQL, any error aborts the whole transaction: it enters a failed state and every subsequent statement returns 'current transaction is aborted' until you roll back. So a write path that inserts, catches the violation, and then wants to continue — update a different row, insert an audit record, commit the rest — must set a **savepoint** before the insert and roll back to it in the handler. Frameworks often do this for you via nested-transaction support, which is implemented with savepoints; you should know that is what is happening, because it also explains why 'nested transactions' cost something. In other engines the failing statement is rolled back and the transaction remains usable. Writing code that assumes this makes it non-portable and, worse, makes it appear to work in tests against one engine. Either way, be explicit: savepoint the risky statement, or structure the flow so the failed insert is the last thing the transaction attempts. ## Sharp edges of the conflict-tolerant form **It may return nothing.** 'Insert, on conflict do nothing' inserts zero rows when a conflict occurs, so a caller that needs the row's identifier gets nothing back and must read it — and that read must be part of the same logical operation, correctly handling the case where the conflicting row was inserted by a transaction that then rolled back. The common pattern is insert-returning, and if no row came back, select. **Sequences and identity columns are not rolled back.** A failed or conflicting insert can still consume the generated value, leaving gaps in the identifier sequence. This is by design — sequences are non-transactional so they do not serialise writers — but it surprises people who expect dense identifiers, and it means an upsert-heavy hot path can burn through values quickly. **It still takes locks and can deadlock.** A conflict-tolerant insert waits on the conflicting row's transaction just as a plain insert does. Two transactions upserting the same set of keys in different orders can deadlock; inserting in a consistent key order avoids it. **Update-on-conflict writes a new row version.** The 'do update' variant is a real write: it produces dead row versions, fires triggers, and touches indexes even when nothing semantically changed. On high-frequency no-op updates, adding a predicate so the update only happens when a value actually differs is a meaningful saving. ## Where idempotency keys fit For externally triggered operations — an API call the client may retry, a payment submission — the durable version of this pattern is an idempotency key: the client supplies a unique token, the server stores it in a column with a unique constraint, and the constraint is what makes the retry safe. The first request inserts the key and performs the work; a retry collides and returns the stored result instead of doing the work twice. The database constraint is the whole mechanism; the application code is just the wrapper that turns the collision into 'you already did this'. ## The summary an interviewer wants Decide by intent, match errors on code plus constraint name, protect the transaction with a savepoint where errors are transaction-fatal, and know that the conflict-tolerant form returns nothing on conflict and still consumes sequence values.