UPSERT with ON CONFLICT, and RETURNING

"Insert this row, or update it if it already exists" is one of the most common writes in any application, and the obvious version is wrong:

SELECT 1 FROM inventory WHERE sku = 'tea';   -- not there...
INSERT INTO inventory VALUES ('tea', 3);     -- ...so insert

Two sessions can both run the SELECT, both see nothing, and both INSERT: one of them then fails with a unique violation, or, without a unique constraint, you get a duplicate. Wrapping it in a transaction at the default READ COMMITTED level does not help, because neither session can see the other's uncommitted row.

ON CONFLICT

INSERT ... ON CONFLICT (cols) DO ... makes the decision inside the insert, using the unique index on cols as the arbiter. If a conflicting row exists, PostgreSQL takes the conflict branch instead of raising an error. If another transaction is inserting the same key at that moment, the statement waits for it to finish, then takes the conflict branch if it committed or inserts if it rolled back. That is documented behaviour at the default READ COMMITTED level; the sandbox runs a single session, so it cannot be shown here. (At REPEATABLE READ and SERIALIZABLE, a conflict with a row your snapshot cannot see raises a serialization failure, 40001, instead.)

• DO NOTHING skips the row.
• DO UPDATE SET ... updates the existing row. In it, the table name (or its alias) refers to the existing row and EXCLUDED to the row you tried to insert.
• An optional WHERE on DO UPDATE updates only when it is true, which is how you write "keep whichever version is newer".

RETURNING works on INSERT, UPDATE and DELETE and returns the rows as written, including defaults and generated ids.

1CREATE TABLE inventory (sku text PRIMARY KEY, qty int NOT NULL, times_updated int NOT NULL DEFAULT 0);2INSERT INTO inventory VALUES ('tea', 5, 0), ('cup', 2, 0);

1INSERT INTO inventory AS i (sku, qty)2VALUES ('tea', 3), ('jar', 4)3ON CONFLICT (sku) DO UPDATE4  SET qty = i.qty + EXCLUDED.qty,5      times_updated = i.times_updated + 16RETURNING sku, qty, times_updated, (xmax = 0) AS was_inserted;

The was_inserted column relies on an implementation detail, not a documented API: a row this statement inserted has xmax 0, while the version written by the DO UPDATE branch came back with a non-zero xmax, a by-product of the row lock that branch takes before updating (xmax is covered with MVCC). It is handy for logging, but nothing guarantees it across versions, so do not build business logic on it.

DO NOTHING and RETURNING

RETURNING only returns rows that were actually inserted or updated, so a skipped row is simply absent. Note also the id below: the skipped row still consumed a value from the identity sequence. Sequences never roll back, so gaps in ids are normal and not a bug.

1CREATE TABLE users (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE);2INSERT INTO users (email) VALUES ('ana@example.com');

1INSERT INTO users (email)2VALUES ('ana@example.com'), ('luka@example.com')3ON CONFLICT (email) DO NOTHING4RETURNING id, email;

What can still go wrong

• The conflict target must match a unique index or constraint exactly, otherwise the statement is rejected (42P10).
• One INSERT ... DO UPDATE may not touch the same row twice. If your input contains the same key twice, aggregate it first (SELECT sku, sum(qty) ... GROUP BY sku).

1CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$2BEGIN3  EXECUTE stmt;4  RETURN 'ok';5EXCEPTION WHEN OTHERS THEN6  RETURN SQLSTATE || ': ' || SQLERRM;7END $$;8CREATE TABLE prices (sku text PRIMARY KEY, price numeric(10,2) NOT NULL, as_of date NOT NULL, note text);9INSERT INTO prices VALUES ('tea', 4.00, '2024-05-01', NULL), ('cup', 9.00, '2024-05-10', NULL);

1CREATE TEMP TABLE outcome AS2SELECT attempt($$INSERT INTO prices VALUES ('tea', 1, '2024-06-01'), ('tea', 2, '2024-06-02')3                 ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price$$) AS same_key_twice,4       attempt($$INSERT INTO prices VALUES ('jam', 1, '2024-06-01')5                 ON CONFLICT (note) DO NOTHING$$) AS no_matching_index;6 7-- Only accept a price that is newer than the one stored.8INSERT INTO prices AS p (sku, price, as_of)9VALUES ('tea', 4.50, '2024-05-07'), ('cup', 8.00, '2024-05-03')10ON CONFLICT (sku) DO UPDATE11  SET price = EXCLUDED.price, as_of = EXCLUDED.as_of12  WHERE EXCLUDED.as_of > p.as_of;13 14SELECT o.same_key_twice, o.no_matching_index,15       (SELECT string_agg(sku || '=' || price, ', ' ORDER BY sku) FROM prices) AS prices_now16FROM outcome o;

PostgreSQL 15 also added MERGE, which can insert, update and delete in one statement from a source table. It is more flexible, but its documentation is explicit that it does not use the arbiter-index mechanism: if a concurrent transaction inserts the same key, MERGE fails with a unique violation instead of switching to an update (again documented, not demonstrated here). For a plain upsert under concurrency, ON CONFLICT is the tool.

Common mistakes

• Select-then-insert in application code, which races under load.
• Feeding a batch with duplicate keys into one ON CONFLICT DO UPDATE and getting error 21000.
• Writing SET qty = qty + 1 and wondering which qty is meant; qualify with the table alias or EXCLUDED.
• Expecting RETURNING after DO NOTHING to return the existing row; it returns nothing for skipped rows.

What interviewers ask

• How do you implement an idempotent insert? A unique constraint plus ON CONFLICT DO NOTHING; explain why a pre-check races.
• What is EXCLUDED? The proposed row that conflicted, available in the DO UPDATE clause.
• Why do ids have gaps? Sequence values are not transactional and are consumed by failed or skipped inserts.