patternsqlpostgresqlModerate

UPSERT with INSERT ... ON CONFLICT DO UPDATE

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

upsertON CONFLICTINSERT OR UPDATEEXCLUDEDidempotent writerace condition

Error Messages

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Problem

Applications that need to insert-or-update rows (upsert) often implement this with a SELECT then INSERT or UPDATE, which is not atomic and causes race conditions under concurrent load.

Solution

Use INSERT ... ON CONFLICT for atomic upsert:

-- Upsert by primary key:
INSERT INTO products (id, name, price, updated_at)
VALUES (42, 'Widget', 9.99, now())
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = EXCLUDED.updated_at;

-- Upsert only if the new value is more recent:
INSERT INTO cache (key, value, updated_at)
VALUES ('session:123', $data, now())
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value,
updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.updated_at > cache.updated_at;

-- Insert and ignore on conflict:
INSERT INTO idempotency_keys (key, created_at)
VALUES ('req_abc123', now())
ON CONFLICT (key) DO NOTHING;

Why

INSERT ... ON CONFLICT is a single atomic operation. The database evaluates the conflict and performs the update or skip within a single lock cycle, eliminating the TOCTOU race condition in application-level read-then-write logic.

Gotchas

ON CONFLICT requires specifying the exact conflict target (column or constraint name)
EXCLUDED refers to the row that was proposed for insertion; use it in the DO UPDATE SET clause
ON CONFLICT DO UPDATE still counts as a write and fires update triggers
For multi-row upserts, all rows share the same ON CONFLICT clause

Code Snippets

Atomic counter increment with upsert

-- Atomic increment (counter upsert):
INSERT INTO page_views (page_id, view_count)
VALUES (7, 1)
ON CONFLICT (page_id) DO UPDATE
  SET view_count = page_views.view_count + 1;

Context

Any write that should update if the row exists or insert if it does not

Revisions (0)

No revisions yet.