Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 90 ยท Querying with PostgreSQL Features

INSERT ON CONFLICT and RETURNING

Learn PostgreSQL upserts with INSERT ON CONFLICT DO NOTHING and DO UPDATE, EXCLUDED, partial-index targets, RETURNING, MERGE in 15+, and RETURNING from MERGE in 17.

"Insert this row, or if it already exists, update it" is one of the most common things an application does: count a page view, save a setting, record that a user saw a message. The naive way is to run a SELECT, then an INSERT or an UPDATE depending on the answer. That has a race condition: two requests can both see "no row" and both try to insert, and one of them fails with a duplicate-key error.

PostgreSQL's answer is INSERT ... ON CONFLICT, a single atomic statement that decides what to do when the insert would violate a unique rule. Its companion is RETURNING, which hands back the rows a statement changed, so you do not need a second query to find out the generated id or the final value.

Good news for this lesson: SQLite supports both features with nearly the same syntax, so most examples run live here. Where PostgreSQL goes further (MERGE, RETURNING from MERGE, ON CONSTRAINT), the blocks are marked read-only.

Two jobs in one statement

ON CONFLICT Decide what happens on a duplicate key.
RETURNING Get the changed rows back immediately.
1 / 2

DO NOTHING

Start with a counter table where each page is unique. Inserting a page that already exists with DO NOTHING simply skips the new row, without raising an error:

SQL DO NOTHING and DO UPDATE on a counter

Press Run (or Ctrl + Enter) to see the result here.

The final table has /home with 11 views (10 plus the 1 from the first upsert; the 99 was skipped) and /pricing with 1. This is the pattern for counters, caches and "ensure this row exists" operations. In PostgreSQL it is guaranteed atomic: concurrent sessions cannot both insert the same key, one of them takes the conflict path instead.

DO NOTHING can be written without a target (ON CONFLICT DO NOTHING), which ignores a violation of any unique index or exclusion constraint. That is convenient but can hide mistakes, so naming the columns is better.

DO UPDATE and EXCLUDED

In DO UPDATE, two row versions are visible:

  • the bare column name (or the table name) refers to the existing row;
  • EXCLUDED (written excluded in SQLite) refers to the row you tried to insert.

So views = views + excluded.views means "existing count plus the amount I was adding". EXCLUDED is also how you say "overwrite with the new value":

SQL
INSERT INTO settings (user_id, key, value)
VALUES (42, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now();

Rules to remember in PostgreSQL:

  • DO UPDATE requires a conflict target: either columns, ON CONFLICT (user_id, key), or ON CONFLICT ON CONSTRAINT settings_pkey. The columns must match a unique index or constraint.
  • You can add a filter: DO UPDATE SET ... WHERE settings.value IS DISTINCT FROM EXCLUDED.value skips the write when nothing would change, which avoids creating a needless new row version.
  • One statement cannot update the same row twice. If a multi-row VALUES list contains the same key twice, PostgreSQL raises "ON CONFLICT DO UPDATE command cannot affect row a second time". Deduplicate the input first.
  • Every conflict-path update still creates a new row version, so very hot counters bloat the table. See MVCC and VACUUM later.
  • A skipped insert may still consume a value from a sequence, so identity or serial ids can have gaps.

RETURNING

RETURNING works on INSERT, UPDATE and DELETE. It returns one row per affected row, with whatever expressions you ask for, including values the database produced such as generated ids and defaults:

SQL RETURNING from INSERT, UPDATE and DELETE

Press Run (or Ctrl + Enter) to see the result here.

The first statement returns three rows with ids 1 to 3 and the default status open, without a separate query to read them back. The update returns the two rows it changed, with their new values. The delete returns the two removed rows, which is handy for logging or moving them elsewhere. At the end only ship is left.

This replaces a classic two-step "insert, then fetch the new id" in application code. In PostgreSQL, the same statement works in a single round trip:

SQL
INSERT INTO orders (customer_id, total)
VALUES (7, 59.90)
RETURNING id, created_at;

RETURNING * is fine in scripts and psql, but name the columns in application code so that adding a column later does not change what you get back.

Combining the two

With DO UPDATE, RETURNING shows the row as it stands after the statement, whether it was inserted or updated. With DO NOTHING, rows skipped because of a conflict are not returned:

SQL RETURNING with conflicts

Press Run (or Ctrl + Enter) to see the result here.

The first insert returns nothing because sql already exists. The second returns 3 | indexes. The third returns 1 | sql (the existing row) and 4 | window (new). That last shape is the standard trick for "get the id of this tag, creating it if needed": a harmless DO UPDATE turns the conflict into a row that RETURNING can report. The price is a dead row version on each conflict, so if most calls hit existing rows, run a plain SELECT first and only insert when nothing is found.

In PostgreSQL you can tell an insert from an update in the returned row with RETURNING (xmax = 0) AS inserted. It relies on an internal system column rather than documented behaviour, so use it for diagnostics, not for logic you cannot afford to break.

Partial indexes as conflict targets

Conflicts happen against a unique index, and a unique index can be partial, covering only some rows. For example, an email must be unique among active subscriptions, but old inactive rows may repeat it. The conflict target then has to repeat the index predicate so that PostgreSQL can infer which index you mean:

SQL A partial unique index as the target

Press Run (or Ctrl + Enter) to see the result here.

The first upsert updates the active [email protected] row (id 1) to the pro plan and leaves the inactive row alone. The second inserts a brand-new row with id 3. In PostgreSQL, active would be a boolean and the clause would read WHERE active. Partial indexes get their own lesson later: Partial and Expression Indexes.

MERGE (PostgreSQL 15 and later)

MERGE is the SQL-standard way to apply a set of source rows to a target table with several outcomes. It reads more like a script than an upsert:

SQL
MERGE INTO stock AS t
USING incoming AS s ON t.sku = s.sku
WHEN MATCHED AND s.qty = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN
  INSERT (sku, qty) VALUES (s.sku, s.qty);

For each source row it finds the matching target row and picks the first fitting WHEN branch, so one statement can update, delete and insert. PostgreSQL 17 added a WHEN NOT MATCHED BY SOURCE branch (for target rows with no source row) and RETURNING, along with a merge_action() function that reports 'INSERT', 'UPDATE' or 'DELETE' for each returned row:

SQL
MERGE INTO stock AS t
USING incoming AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action(), t.sku, t.qty;

Which should you choose? Use ON CONFLICT for the classic single-row upsert, especially under concurrency: it guarantees one of the two outcomes without errors. MERGE does not retry on a concurrent insert, so two sessions inserting the same new key can still hit a unique violation. Use MERGE when you need several actions (delete, conditional update) or portability with other databases. PostgreSQL 18 also lets RETURNING refer to OLD and NEW values, so an update can return both the before and after state.

Chaining with CTEs

RETURNING combines with WITH to move rows in one statement. This archives and deletes in a single step, so a failure cannot do one without the other:

SQL
WITH removed AS (
  DELETE FROM sessions
  WHERE last_seen < now() - interval '30 days'
  RETURNING *
)
INSERT INTO sessions_archive
SELECT * FROM removed;

Writable CTEs are covered later in CTEs, Materialization and Recursive Queries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In ON CONFLICT (page) DO UPDATE SET views = views + excluded.views, what does excluded.views refer to?

    EXCLUDED is the proposed row. The bare column name is the existing row, so the statement adds the new amount to the stored one.
  2. 2. What does INSERT ... ON CONFLICT DO NOTHING RETURNING id return when the row already exists?

    RETURNING only reports rows that were actually inserted. A skipped conflict produces no row, so you need a SELECT, or a no-op DO UPDATE, to get the existing id.
  3. 3. Why is INSERT ... ON CONFLICT safer than SELECT followed by INSERT or UPDATE?

    The check and the write happen together. Two requests racing for the same key cannot both insert, because one takes the conflict path.
  4. 4. Which statement is true about MERGE in PostgreSQL?

    MERGE was added in PostgreSQL 15 and gained RETURNING and merge_action() in 17. Unlike ON CONFLICT it does not handle concurrent inserts of the same key gracefully.
  5. 5. A unique index is defined with WHERE active. How must the conflict target look?

    The target repeats the index predicate so PostgreSQL can infer which partial unique index applies.

Summary

  • INSERT ... ON CONFLICT is an atomic upsert: DO NOTHING skips the row, DO UPDATE changes the existing one.
  • DO UPDATE needs a conflict target (columns or ON CONSTRAINT), and EXCLUDED holds the row you tried to insert.
  • Add a WHERE to DO UPDATE to skip writes that change nothing; duplicate keys inside one statement raise an error.
  • RETURNING hands back inserted, updated or deleted rows, including generated ids and defaults; DO NOTHING returns nothing for skipped rows.
  • Partial unique indexes work as targets if the target repeats the index predicate.
  • MERGE (15+) handles multi-branch logic, and 17 adds RETURNING with merge_action(); for plain upserts under concurrency, prefer ON CONFLICT.
  • Upserts and conflicts leave dead row versions and sequence gaps, which later lessons explain.

Next: Full-Text Search, searching documents by words instead of by exact text.