0

ON CONFLICT & upsert

SQLused in ~80% of apps

DevLog's autosave has to run the same insert every few seconds while alice keeps typing her draft. The naive fix — check if it exists, then INSERT or UPDATE — has a race baked into it: two requests can both check, both find nothing, and both insert, leaving two rows where you meant one. ON CONFLICT closes that gap by making the check and the write a single atomic statement.

Core idea. INSERT ... ON CONFLICT (columns) DO NOTHING | DO UPDATE lets one statement try an insert and, if it collides with an existing UNIQUE or PRIMARY KEY constraint, fall back to either doing nothing or updating the row that was already there — using EXCLUDED to mean 'the row I was about to insert.' The conflict target must name a real constraint; there is no such thing as ON CONFLICT against an arbitrary column.
Playground, this isn't a screenshot
on conflict, key('views') do
the row (key = 'views')
1
counters.n
0 attempts
what happened
click insert to see the fork fire
The fork only exists because of the constraint. Without a UNIQUE or PRIMARY KEY on key, there is nothing for ON CONFLICT (key)to name — Postgres would refuse the whole statement with 42P10 before it ever tried DO NOTHING or DO UPDATE.
↑ play a little first, then dig in