INSERT … ON CONFLICT lets PostgreSQL resolve an insert collision inside the statement that performs the write. It replaces a fragile “check whether the key exists, then insert” sequence with an explicit conflict policy backed by an eligible index or constraint. It does not turn every insertion error into a successful update or define the application’s merge policy for you.

The conflict target defines the race

A target such as (name) asks PostgreSQL to infer a matching unique index. The table must actually have an eligible uniqueness rule; writing ON CONFLICT does not create one. Index expressions and partial-index predicates matter when the key is more complicated than a simple column. An unmatched target raises an error instead of silently falling back to another strategy.

DO UPDATE requires a target, and its arbiter must be a supported nondeferrable unique constraint or unique index. Exclusion constraints are not supported as arbiters for DO UPDATE. DO NOTHING has broader conflict-handling options, including supported exclusion constraints, and may omit a target to handle conflicts with usable arbiters. None of these rules makes foreign-key, not-null, or check violations disappear.

The atomic insert-or-update behavior protects against the concurrent key race. It can still wait for another transaction holding a relevant row or index conflict, and normal transaction errors can still occur. Atomicity describes the outcome’s coordination, not a promise that the statement is lock-free, never blocks, or cannot need transaction-level retry handling.

Write the merge policy explicitly

The target table name or alias refers to the existing row. EXCLUDED represents the proposed insertion, including relevant effects of BEFORE INSERT triggers. Assigning EXCLUDED.total replaces the old value; assigning the existing total plus EXCLUDED.total adds a contribution. These policies answer different business questions, even though both use the same SQL syntax.

For a counter, an update expression using the existing row avoids calculating a replacement value from an earlier client-side read. For a versioned document, a WHERE condition can reject an older proposed version. Such a condition is evaluated after identifying and locking the conflicting row; a rejected update can therefore still contend with other writers.

RETURNING emits only rows actually inserted or updated. DO NOTHING returns no row for the existing record, and DO UPDATE with a false WHERE condition also returns no row. An empty result is therefore not uniquely diagnostic of DO NOTHING. Define the caller’s interpretation of skipped work instead of treating every empty result as a missing database record.

When it breaks

A batch can contain two proposed rows with the same conflict key. DO UPDATE cannot affect one existing row twice in the same statement and raises a cardinality violation in that case. Combine or reject duplicate inputs according to the intended policy before submitting the batch. Arbitrarily keeping the last input can silently change an additive operation into replacement semantics.

ON CONFLICT also does not imply idempotency. Retrying an additive upsert after an uncertain response can add the same contribution again. A separate request identity and its uniqueness contract are needed when one logical request must be applied once. Likewise, an unconditional replacement can overwrite a newer value if an older request arrives later.

Errors from other constraints remain important. A conflicting key does not make invalid new values harmless: PostgreSQL can reject a CHECK violation before taking DO NOTHING. Sequence defaults may also be evaluated before conflict resolution, so skipped insertions can consume sequence numbers. Neither behavior means the conflict handler malfunctioned.

Check it quickly

Save this as upsert.sql and run psql -X -v ON_ERROR_STOP=1 -f upsert.sql in a disposable PostgreSQL 18 database. The temporary counter table exists only within the test transaction, which is rolled back at the end.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE tally (
  name text PRIMARY KEY,total integer NOT NULL CHECK (total>=0),version integer NOT NULL
);
INSERT INTO tally VALUES ('jobs',5,1) RETURNING *;
INSERT INTO tally VALUES ('jobs',3,2)
ON CONFLICT (name) DO UPDATE
SET total=tally.total+EXCLUDED.total,version=EXCLUDED.version
RETURNING *;
WITH changed AS (
  INSERT INTO tally VALUES ('jobs',100,3) ON CONFLICT (name) DO NOTHING RETURNING *
) SELECT count(*) AS do_nothing_returned FROM changed;
WITH changed AS (
  INSERT INTO tally VALUES ('jobs',100,1)
  ON CONFLICT (name) DO UPDATE SET total=EXCLUDED.total,version=EXCLUDED.version
  WHERE tally.version<EXCLUDED.version RETURNING *
) SELECT count(*) AS stale_update_returned FROM changed;
DO $$ BEGIN
  INSERT INTO tally VALUES ('jobs',-1,4) ON CONFLICT (name) DO NOTHING;
  RAISE EXCEPTION 'check violation was swallowed';
EXCEPTION WHEN check_violation THEN RAISE NOTICE 'CHECK still rejects invalid input';
END $$;
DO $$ BEGIN
  INSERT INTO tally VALUES ('jobs',1,4),('jobs',2,5)
  ON CONFLICT (name) DO UPDATE SET total=EXCLUDED.total;
  RAISE EXCEPTION 'same row updated twice';
EXCEPTION WHEN cardinality_violation THEN RAISE NOTICE 'duplicate input keys rejected for DO UPDATE';
END $$;
SELECT * FROM tally;
DO $$ BEGIN
  IF (SELECT total FROM tally WHERE name='jobs') IS DISTINCT FROM 8 OR
     (SELECT version FROM tally WHERE name='jobs') IS DISTINCT FROM 2 THEN
    RAISE EXCEPTION 'conflict action changed the wrong state';
  END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 inserted total five at version one, then added three and produced total eight at version two. Both the DO NOTHING case and the stale conditional update returned zero rows. The negative CHECK input and duplicate-key batch raised their expected errors, which the fixture catches specifically rather than swallowing arbitrary failures.

The final assertion confirms that rejected work did not replace the total or version. This example exercises the distinct conflict paths; it is not a latency benchmark or a guarantee that arbitrary merge expressions are safe. Review the update expression, the target uniqueness rule, and the caller’s retry semantics together before using the statement in a concurrent workflow.

Sources

  • PostgreSQL INSERT, arbiters, atomic conflict handling, EXCLUDED, and RETURNING, checked 2026-09-10.
  • PostgreSQL constraints, uniqueness, checks, nullability, and referential integrity, checked 2026-09-10.