阅读中文版本

Upsert with INSERT ... ON CONFLICT

Upsert inserts a row or updates it when a unique constraint is violated, collapsing a race-prone read-modify-write into one atomic statement.

Definition

An upsert is a single statement that inserts a new row and, if a uniqueness constraint would be violated, instead updates the existing row or ignores the conflict.

It is the standard way to implement idempotent writes, counters, and last-writer-wins state without a separate select followed by insert or update. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

The conflict target must match a unique index, and the update clause can reference the proposed values by their virtual row name, so both insert and update paths use one statement shape.

Because the operation is atomic at the statement level, concurrent callers do not lose data the way a read-then-write in application code can.

Trade-offs and alternatives

Under high concurrency, competing upserts can still block on the same key, so batch writes and keep transactions short to avoid lock convoying. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that upsert never needs a unique constraint. Another is that on conflict do update always returns the newly updated row to every caller. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to doing a select then an insert in application code under concurrency and to including the target column in the update set and creating a no-op. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include The RETURNING Clause, GIN Indexes for Composite Values, Trigram Indexes for Fuzzy Matching.