Writing an upsert with INSERT ... ON CONFLICT

Goal

Insert a row when its key is new and update the existing row otherwise, without a race between SELECT and INSERT and without retry loops in application code.

Prerequisites

A unique index or constraint on the key columns (the INSERT documentation calls the index chosen for conflict detection the arbiter index and describes how it is inferred from the listed columns, an index predicate, or ON CONSTRAINT name), and a decision about which columns an update may overwrite.

Steps

1. Write the plain INSERT with all columns.
2. Append ON CONFLICT (key_columns) naming the columns of the unique index. For a partial unique index, repeat its WHERE predicate after the column list so that inference finds it.
3. Choose the action: DO NOTHING when an existing row must stay untouched; DO UPDATE SET col = EXCLUDED.col, updated_at = now() to overwrite selected columns. EXCLUDED is the row that was proposed for insertion.
4. Guard no-op updates with WHERE t.col IS DISTINCT FROM EXCLUDED.col after the SET list, so unchanged rows are not rewritten; every update creates a dead row version and fires triggers.
5. Add RETURNING id when the caller needs the key of the inserted or updated row.
6. For batches, use one INSERT with many VALUES rows or INSERT ... SELECT; do not loop over single-row upserts.
7. When the logic has several branches (update if matched and condition, delete if matched otherwise, insert if not matched), write MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...; the MERGE documentation lists WHEN MATCHED, WHEN NOT MATCHED and WHEN NOT MATCHED BY SOURCE actions.

Expected result

One statement per upsert, no duplicate rows under concurrent writers because the conflict is detected on the unique index inside the statement, and dead-row churn limited to rows that actually changed.

Limits and test basis

ON CONFLICT DO UPDATE requires a conflict target; only conflicts on the arbiter indexes it selects are handled (a violation of another unique index still raises an error), and only NOT DEFERRABLE constraints and unique indexes can serve as arbiters. DO NOTHING without a conflict target handles conflicts with all usable constraints. The sequence documentation states that an INSERT with an ON CONFLICT clause computes the row, including nextval calls, before detecting the conflict, so identity values are consumed by rows that end up not inserted. The MERGE documentation says the usual isolation rules apply under concurrency and points to INSERT ... ON CONFLICT as the statement that can run an UPDATE when a concurrent INSERT occurs; the two are not interchangeable. No timings are claimed.

When MERGE is the right tool

MERGE and INSERT ... ON CONFLICT are not interchangeable under concurrency. MERGE evaluates its match against a snapshot and does not perform the speculative insertion that ON CONFLICT relies on, so two sessions merging the same new key can both take the WHEN NOT MATCHED branch, and the second fails with a unique violation. Use MERGE when one writer owns the key range at a time: batch loads, migrations, single-threaded importers, or rows locked in advance. Use ON CONFLICT whenever concurrent writers are possible; several update branches fit in CASE expressions in the SET list, and a delete branch becomes a separate statement. WHEN NOT MATCHED BY SOURCE and RETURNING on MERGE require PostgreSQL 17 or later.