Upsert (INSERT ... ON CONFLICT)
How do you upsert in PostgreSQL with INSERT ... ON CONFLICT?
INSERT ... ON CONFLICT inserts a row or, if that would violate a unique constraint, updates the existing row or skips it, as one atomic step.
Upsert means insert or update. PostgreSQL implements it with INSERT ... ON CONFLICT: when the new row would violate a unique or exclusion constraint, DO NOTHING skips it and DO UPDATE updates the conflicting row instead.
ON CONFLICT DO UPDATE guarantees an atomic insert-or-update outcome even with concurrent sessions, so you do not need a separate SELECT followed by INSERT or UPDATE. The special EXCLUDED table refers to the row that was proposed for insertion. The conflict target, such as (path), must match a unique index or constraint.
Example
The example runs the same upsert twice. The first inserts /home with 1 view; the second hits the primary key and adds 1, so the final count is 2.
CREATE TABLE page_views (path text PRIMARY KEY, views bigint NOT NULL);
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
SELECT * FROM page_views;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).