"Postgres ON CONFLICT DO SELECT: Atomic Get-or-Create in PG19"
Postgres ON CONFLICT DO SELECT in PostgreSQL 19 beta — atomic get-or-create that returns the existing row without dead tuples, with RETURNING syntax and FOR UPDATE locking.
Postgres on conflict do select is the third action PostgreSQL 19 adds to INSERT ... ON CONFLICT : alongside DO NOTHING and DO UPDATE , you can now write DO SELECT — which returns the conflicting row instead of inserting, atomically, without writing a single dead tuple. If you've ever written a SELECT -then- INSERT get-or-create and watched it blow up under concurrency, or used the DO UPDATE ... SET col = EXCLUDED.col no-op hack just to get the row back, this is the feature that deletes both workarounds. It's shipping in PG19 — with a beta flag, because PG19 itself is still beta. BETA FLAG: PostgreSQL 19 is still in beta (beta4 shipped 2026-09-24; RC1 scheduled 2026-10-15; GA scheduled 2026-10-29). ON CONFLICT DO SELECT is confirmed still shipping as of beta4, but don't use it in production until GA — and verify the final syntax against the PG19 release notes. Last updated: October 4, 2026 What you'll learn Why SELECT -then- INSERT get-or-create is broken under concurrency The ON CONFLICT DO SELECT syntax (and why RETURNING is mandatory) How it compares to DO NOTHING and DO UPDATE — including the dead-tuple cost Row locking with DO SELECT FOR UPDATE and the xmax = 0 trick to tell inserted from existing rows The security and privilege edge cases to know about The Problem: Atomic Get-or-Create in Postgres Almost every app has a get-or-create somewhere: tags, users by email, inventory SKUs, OAuth identities. The naive version is a check-then-insert: -- session A and session B run this "at the same time" SELECT id FROM tags WHERE name = 'postgres'; -- both see zero rows... INSERT INTO tags (name) VALUES ('postgres'); -- ...and one of them eats a unique violation Between the SELECT and the INSERT there's a gap, and concurrent…