TopolearnSyncing data, counters and idempotent APIs all need 'insert or update' without race conditions.
"Insert unless it exists, otherwise update" looks like a SELECT followed by an INSERT or UPDATE. Under concurrency that is a race: two requests both see no row and both insert, and one fails or duplicates data. Do it in one atomic statement instead.
PostgreSQL's upsert is INSERT ... ON CONFLICT:
INSERT INTO customers (email, name) VALUES ('ana@example.com', 'Ana K.')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;- The conflict target
(email)must match a unique index or constraint; that index is what detects the conflict. EXCLUDEDrefers to the row you tried to insert; the plain table name refers to the existing row. That allows increments:SET qty = daily_sales.qty + EXCLUDED.qty.DO UPDATE ... WHEREupdates only when a condition holds, for example only if the incoming data is newer.DO NOTHINGsilently skips conflicting rows, which makes inserts idempotent.
One multi-row INSERT cannot touch the same row twice: if two input rows share an email, PostgreSQL raises "ON CONFLICT DO UPDATE command cannot affect row a second time". De-duplicate the input first.
MERGE (SQL standard; PostgreSQL 15+, SQL Server, Oracle) is more general: it joins a source to a target and runs different actions WHEN MATCHED and WHEN NOT MATCHED, including deletes. It is ideal for batch synchronisation from a staging table. In PostgreSQL, however, MERGE is not protected against concurrent inserts the way ON CONFLICT is: two concurrent MERGEs can both decide to insert, and one then fails with a unique violation. For concurrent single-row upserts, prefer ON CONFLICT.
MySQL's equivalent is INSERT ... ON DUPLICATE KEY UPDATE; SQLite supports PostgreSQL's ON CONFLICT syntax.
Example
CREATE TABLE daily_sales (
product_id bigint REFERENCES products (id),
day date,
qty integer NOT NULL,
PRIMARY KEY (product_id, day)
);
-- Atomic counter: insert the first sale, increment afterwards
INSERT INTO daily_sales (product_id, day, qty) VALUES (2, '2026-03-15', 1)
ON CONFLICT (product_id, day)
DO UPDATE SET qty = daily_sales.qty + EXCLUDED.qty
RETURNING qty; -- 1 the first time, 2 the second time, ...
-- Idempotent insert
INSERT INTO customers (name, email) VALUES ('Ana', 'ana@example.com')
ON CONFLICT (email) DO NOTHING; -- INSERT 0 0
-- Batch sync from a staging table (PostgreSQL 15+)
MERGE INTO products p
USING staged_products s ON s.sku = p.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);Remember
- Never SELECT-then-INSERT for upserts; it races under concurrency
- ON CONFLICT needs a unique index on the conflict target; EXCLUDED is the proposed row
- DO NOTHING makes inserts idempotent
- MERGE suits batch syncs; ON CONFLICT is safer for concurrent upserts in PostgreSQL
Watch out for
- Including the same key twice in one multi-row upsert, which fails with 'cannot affect row a second time'.