Upsert with INSERT ... ON CONFLICT (PostgreSQL)
Insert a row or update it if the unique key already exists, atomically and safely under concurrency.
- SQL
- GreenSnippets
- MIT
- Executed with PostgreSQL 17
INSERT INTO page_views (path, views)
VALUES ('/pricing', 1)
ON CONFLICT (path)
DO UPDATE SET views = page_views.views + EXCLUDED.views
RETURNING path, views;How it works
ON CONFLICT makes "insert or update" a single atomic statement, avoiding the race in a separate SELECT then INSERT. The conflict target must match a unique index or constraint. EXCLUDED refers to the row you tried to insert.
Use DO NOTHING when you only want to ignore duplicates. RETURNING gives you the final row either way.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.