How does INSERT ... ON CONFLICT work in PostgreSQL?

Summary

It is PostgreSQL's atomic upsert: insert a row, and if it violates a specified unique constraint or index, either do nothing (DO NOTHING) or update the existing row (DO UPDATE SET ..., with EXCLUDED referring to the proposed row).

Longer Answer

INSERT INTO page_views (page_id, day, views)
VALUES (42, current_date, 1)
ON CONFLICT (page_id, day) DO UPDATE SET views = page_views.views + EXCLUDED.views
RETURNING views;

The conflict target must match a unique index (or constraint). It is safe under concurrency, unlike "select, then insert or update". DO UPDATE can affect each row only once per statement, so deduplicate input batches. MERGE (PostgreSQL 15+) handles more complex insert/update/delete synchronisation; it is not a drop-in concurrency-safe replacement for ON CONFLICT.

Key Points

• Atomic upsert on a unique index.
• EXCLUDED = proposed row.
• Concurrency-safe.
• MERGE for complex sync (PG 15+).