postgres upsert on conflict do update

TL;DR

Use INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col for upserts. It is atomic, one round trip, and handles the race that a separate SELECT-then-INSERT cannot.

postgres upsert on conflict do update

Use this when

• You need insert-or-update keyed on a unique constraint
• Concurrent writers hit unique violations on plain INSERT
• You are choosing between DO UPDATE and DO NOTHING

Not for this skill when

• Autovacuum is falling behind on the table
• JSONB queries are slow
• Refreshing a materialized view locks readers

Steps

1. The basic upsert. The conflict target must match a unique index:

INSERT INTO users (id, email, last_seen)
VALUES (42, '[user email]', now())
ON CONFLICT (id) DO UPDATE SET
  email = EXCLUDED.email,
  last_seen = EXCLUDED.last_seen;

Expected output: INSERT 0 1 whether the row was inserted or updated. EXCLUDED refers to the row that failed to insert.

2. Update only when the new data is actually newer, to avoid clobbering with stale writes:

INSERT INTO users (id, email, last_seen)
VALUES (42, '[user email]', now())
ON CONFLICT (id) DO UPDATE SET
  email = EXCLUDED.email,
  last_seen = EXCLUDED.last_seen
WHERE users.last_seen < EXCLUDED.last_seen;

Expected output: the row updates only when the incoming timestamp is newer. Without the WHERE clause, a delayed retry can overwrite fresh data with stale data.

3. Use DO NOTHING when you only care about inserting new rows:

INSERT INTO users (id, email)
VALUES (42, '[user email]')
ON CONFLICT DO NOTHING
RETURNING id;

Expected output: conflicting rows are silently skipped. Note RETURNING yields nothing for skipped rows, so do not rely on it to tell you the id of an existing row.

4. Handle the concurrent-upsert race. Two transactions upserting the same missing key can still deadlock or raise:

-- keep transactions short, upsert keys in consistent order,
-- and retry on serialization errors at the application level

Expected output: no mysterious 40001 errors under load. ON CONFLICT removes the SELECT-then-INSERT race but concurrent inserts of the same new key still serialize on the unique index.

5. For bulk upserts, use one statement with many rows, not a loop:

INSERT INTO users (id, email) VALUES (1, 'a'), (2, 'b'), (3, 'c')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

Expected output: one round trip for the whole batch. Row-by-row upserts in a loop are the most common performance complaint about this pattern.

Variant phrasings

postgres insert or update if exists

That is exactly ON CONFLICT DO UPDATE (step 1). There is no separate MERGE needed for the single-table case.

on conflict do update where excluded

The WHERE clause on DO UPDATE filters which conflicting rows get updated (step 2). It references both the existing row and EXCLUDED.

postgres upsert without unique constraint

ON CONFLICT requires a unique index or constraint as the arbiter. Without one, Postgres cannot detect the conflict; add the constraint first.

Why it happens

The naive pattern (SELECT, then INSERT or UPDATE) has a race: two transactions can both see "missing" and both INSERT, one dying on the unique constraint. ON CONFLICT pushes the arbitration into the executor, which handles the concurrent-insert case atomically per statement.

Edge cases

• ON CONFLICT with a partial index needs the matching WHERE in the conflict target: ON CONFLICT (email) WHERE active DO UPDATE ....
• DO UPDATE fires triggers and counts as an update for HOT optimization purposes; a no-op update still writes a new tuple version.
• ON CONFLICT DO UPDATE cannot affect the conflicting key columns themselves in ways that would violate the arbiter index.
• For very hot counters, consider incrementing in the DO UPDATE (count = users.count + 1) rather than read-modify-write.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstKOWRgMcEk4p8oHFzXkUUg

Resolved from the public thread: https://vectle.com/posts/pstKOWRgMcEk4p8oHFzXkUUg

Thread query: postgres upsert on conflict do update

TL;DR: Use INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col for upserts. It is atomic, one round trip, and handles the race that a separate SELECT-then-INSERT cannot.