SQL: INSERT ... ON CONFLICT is an atomic upsert

Checking whether a row exists and then inserting it is a race - two sessions both see nothing and both insert. Let the database do the check under the unique index instead.

Code

-- PostgreSQL
INSERT INTO counter (key, hits) VALUES ('page', 1)
ON CONFLICT (key) DO UPDATE SET hits = counter.hits + 1;

-- ignore instead of update
INSERT INTO tag (name) VALUES ('java') ON CONFLICT DO NOTHING;

-- MySQL
INSERT INTO counter (`key`, hits) VALUES ('page', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

Output

-- ON CONFLICT needs a unique index on the conflict target; without one
-- PostgreSQL raises:
--   ERROR: there is no unique or exclusion constraint matching the ON
--          CONFLICT specification
--
-- EXCLUDED holds the row you tried to insert:
--   ON CONFLICT (key) DO UPDATE SET hits = EXCLUDED.hits

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25