Upsert in one statement

A common bug: run a SELECT to see if a row exists, then INSERT or UPDATE depending on the answer. Between the two statements another connection can do the same check, and you get a duplicate-key error or a duplicate row.

INSERT ... ON CONFLICT, often called upsert, does both in one atomic statement.

DO UPDATE

INSERT INTO <table> (cols...) VALUES (...)
ON CONFLICT (<conflict_target>) DO UPDATE
SET col = <expression>;

The conflict target is a column or set of columns with a UNIQUE constraint or primary key. Postgres needs that index to detect the conflict.

Two names matter in the SET list:

table.col is the value in the existing row.
EXCLUDED.col is the value from the row you tried to insert.

INSERT INTO page_views (page, user_email, views)
VALUES ('/home', 'ada@example.com', 1)
ON CONFLICT (page, user_email) DO UPDATE
SET views = page_views.views + 1;

If the pair is new, the row is inserted with views = 1. If it exists, views goes up by one. There is no window for a race.

DO NOTHING

When "update" would mean "ignore it", use DO NOTHING:

INSERT INTO tags (name) VALUES ('postgres'), ('sql')
ON CONFLICT (name) DO NOTHING;

Existing tags stay as they are, and no error is raised.

Practice: Count a visit.

Notes for this site

PostgreSQL 15 added MERGE for cases where ON CONFLICT is too narrow, such as updating and deleting in one statement. Our engine is PostgreSQL 19, so both are available.