Upserts and Returning

ON CONFLICT DO NOTHING

INSERT ... ON CONFLICT turns a would-be unique violation into a defined action. DO NOTHING skips the row and reports success.

-- Any unique or exclusion constraint on the table
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT DO NOTHING;

-- Only this conflict, anything else still raises 23505
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT (name) DO NOTHING;

The bare ON CONFLICT DO NOTHING swallows a violation on every constraint, including one you did not think about. Naming a conflict target is the safer default.

DO NOTHING returns nothing, so RETURNING gives zero rows when the row already existed. That surprises every ORM at least once. To get the id either way, upsert with DO UPDATE, or fall back to a select.

-- Insert or fetch, in one round trip
WITH ins AS (
  INSERT INTO tags (name) VALUES ($1)
  ON CONFLICT (name) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM tags WHERE name = $1
LIMIT 1;

ON CONFLICT DO UPDATE

The real upsert. Inside DO UPDATE the pseudo-table EXCLUDED holds the row you tried to insert, and the table name refers to the row already stored.

INSERT INTO counters (key, n, updated_at)
VALUES ($1, 1, now())
ON CONFLICT (key) DO UPDATE
  SET n          = counters.n + EXCLUDED.n,
      updated_at = EXCLUDED.updated_at
RETURNING key, n;

A WHERE on the conflict action makes the update conditional. The row is left alone when it is false, and RETURNING yields nothing for it.

-- Only move the counter forward, never backward
INSERT INTO device_state (device_id, seq, payload)
VALUES ($1, $2, $3)
ON CONFLICT (device_id) DO UPDATE
  SET seq = EXCLUDED.seq, payload = EXCLUDED.payload
  WHERE device_state.seq < EXCLUDED.seq
RETURNING seq;

There is also an index predicate WHERE that belongs to the conflict target, before DO. The two are different clauses in different positions.

INSERT INTO t (a, b) VALUES (1, 2)
ON CONFLICT (a) WHERE b > 0        -- index predicate, picks a partial index
DO UPDATE SET b = EXCLUDED.b
WHERE t.b <> EXCLUDED.b;           -- action predicate, decides whether to update

Conflict targets

-- Named constraint
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET name = EXCLUDED.name;

-- Partial unique index, soft deletes allowed to repeat
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;
INSERT INTO users (email) VALUES ($1)
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

-- Expression index
CREATE UNIQUE INDEX users_lower_email ON users (lower(email));
INSERT INTO users (email) VALUES ($1)
ON CONFLICT (lower(email)) DO UPDATE SET email = EXCLUDED.email;

ON CONFLICT ON CONSTRAINT only accepts a table constraint (UNIQUE, PRIMARY KEY, EXCLUDE), never a bare CREATE UNIQUE INDEX, and it is the only form that works against an exclusion constraint, and then only with DO NOTHING.

Two errors worth recognizing:

ERROR:  there is no unique or exclusion constraint matching
        the ON CONFLICT specification

The column list must match an existing unique index's columns exactly.

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:   Ensure that no rows proposed for insertion within the same command
        have duplicate constrained values.

One statement inserted two rows with the same key. Deduplicate the batch first, for example with SELECT DISTINCT ON (key) ... over the incoming rows.

MERGE, and when to prefer it

MERGE (15+) is the SQL-standard form. It joins a source to a target and picks a branch per row, which lets one statement insert, update, and delete.

MERGE INTO counters t
USING staged_counters s ON t.key = s.key
WHEN MATCHED AND s.n = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET n = t.n + s.n, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (key, n) VALUES (s.key, s.n);

WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED BY TARGET (17+) make full synchronization possible, and RETURNING with merge_action() is also 17+.

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED BY TARGET THEN INSERT (id, v) VALUES (s.id, s.v)
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN MATCHED THEN UPDATE SET v = s.v
RETURNING merge_action(), t.id, t.v;

The blunt rule: for a concurrent single-row upsert on a unique key use ON CONFLICT, because it is atomic against concurrent inserters. For a batch reconcile of a staging table against a target, and especially when rows must be deleted, use MERGE. MERGE does not take special locks, so a concurrent insert between its join and its INSERT branch still produces a 23505 that your code has to retry.

RETURNING

RETURNING works on INSERT, UPDATE, DELETE, and MERGE (17+). It takes any select list, including expressions, and it reads the row after defaults, generated columns, and triggers have applied, which saves the extra round trip.

INSERT INTO orders (customer_id, total)
VALUES ($1, $2)
RETURNING id, created_at, total * 1.2 AS with_tax;

UPDATE orders SET status = 'shipped', shipped_at = now()
WHERE id = $1 AND status = 'paid'
RETURNING id, status, shipped_at;

DELETE FROM sessions WHERE expires_at < now()
RETURNING id, user_id;

INSERT INTO t (a) SELECT a FROM staging RETURNING *;   -- works on multi-row inserts

UPDATE ... RETURNING returning zero rows is how you detect "not found or not in the expected state" without a separate SELECT, which is the cheapest form of optimistic concurrency control.

From 18 RETURNING can name OLD and NEW explicitly, so an update can hand back both sides in one statement:

UPDATE accounts SET balance = balance - 50 WHERE id = $1
RETURNING OLD.balance AS before, NEW.balance AS after;   -- 18+

On 15 through 17, get the previous value with a self-join through a CTE instead:

WITH prev AS (SELECT id, balance FROM accounts WHERE id = $1 FOR UPDATE)
UPDATE accounts a SET balance = a.balance - 50
FROM prev
WHERE a.id = prev.id
RETURNING prev.balance AS before, a.balance AS after;

Did it insert or update?

ON CONFLICT DO UPDATE returns a row either way and does not say which branch ran. The system column xmax is zero for a freshly inserted tuple and non-zero for one your statement updated, which is the standard trick.

INSERT INTO counters (key, n) VALUES ($1, 1)
ON CONFLICT (key) DO UPDATE SET n = counters.n + 1
RETURNING key, n, (xmax = 0) AS was_insert;

It is a documented-enough idiom to be everywhere in production code, but it reads an internal column, so treat it as a diagnostic rather than a contract. The portable alternative is to compare a timestamp:

RETURNING key, n, (created_at = updated_at) AS was_insert;

Two more things that bite:

- A conflicting INSERT ... ON CONFLICT DO NOTHING still consumes a sequence value, so serial and identity columns develop gaps. That is by design, sequences are not gapless. - ON CONFLICT DO UPDATE fires BEFORE INSERT triggers, then the UPDATE triggers, and it requires UPDATE privilege on the table plus SELECT on the conflict columns.