How does ON CONFLICT (cols) DO UPDATE SCALE retries safely?

Category: SQL for AI Engineering

Answer

INSERT … ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col makes the statement idempotent: re-run safely without duplicate rows. EXCLUDED.col is the value the conflicting insert WOULD have set. Combine with RETURNING to get the persisted row.

Concrete examples from the fca project context

Example 1

Ingest pipeline: insert chunks with id = checksum(chunk). ON CONFLICT (id) DO NOTHING -> idempotent re-ingest.

Example 2

Profile updates: ON CONFLICT (user_id) DO UPDATE SET last_seen = EXCLUDED.last_seen.

Example 3

RETURNING id: get the row id in one round-trip.

Data flow / flow chart

INSERT (col1, col2)
  ON CONFLICT (col1) DO UPDATE SET col2 = EXCLUDED.col2
  RETURNING id

Takeaway

ON CONFLICT is the safe primitive for any “insert but don’t duplicate” pipeline.