PostgreSQL UPSERT (ON CONFLICT)

What is UPSERT?

UPSERT is a combination of INSERT and UPDATE. In PostgreSQL it is implemented with INSERT ... ON CONFLICT. If an insert would violate a unique constraint, you can update the existing row or do nothing.

Syntax

INSERT INTO table_name (columns) VALUES (values)
ON CONFLICT (conflict_column)
DO UPDATE SET column = EXCLUDED.value;

INSERT INTO table_name (columns) VALUES (values)
ON CONFLICT (conflict_column)
DO NOTHING;

When to Use

• Syncing data from an external source (insert new, update existing)
• Idempotent operations that should not fail on duplicates
• Incrementing counters on duplicate keys
• Handling race conditions in concurrent inserts

Key Points

1. EXCLUDED — In DO UPDATE, EXCLUDED refers to the values proposed for insertion.
2. Conflict Target — Specify the unique column(s) or constraint.
3. DO NOTHING — Silently skip the row on conflict.
4. DO UPDATE — Update the existing row with new values.
5. WHERE in DO UPDATE — You can add a WHERE clause to conditionally update.
6. RETURNING — Combine with RETURNING to see the result.