Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 33 ยท Changing Data

UPSERT and RETURNING

Handle duplicate keys in SQLite with INSERT OR IGNORE, OR REPLACE and ON CONFLICT DO UPDATE, use the excluded table, and read results back with RETURNING.

Sooner or later an INSERT collides with a row that already exists: a user signs up twice, a counter needs bumping, a sync job re-sends the same record. Without help you would write "check, then insert or update", which is slow and racy when two programs run it at once. SQLite lets the database decide in one statement, using conflict handling on the INSERT itself.

This lesson covers the three tools: the older OR IGNORE and OR REPLACE shortcuts, the modern ON CONFLICT ... DO UPDATE clause (called an upsert), and the RETURNING clause that gives you the affected rows back. It builds on INSERT, UPDATE and DELETE and on the constraints from Constraints and STRICT tables.

A conflict needs a decision

IGNORE Skip the new row, keep the old one.
REPLACE Delete the old row, insert the new one.
DO UPDATE Keep the row, change chosen columns.

DO UPDATE is the safest of the three.

1 / 2

What counts as a conflict

A conflict happens when an insert or update would break a UNIQUE or PRIMARY KEY constraint (and, with a different clause, a NOT NULL or CHECK constraint). Plain INSERT aborts the statement with an error, which is the right default most of the time. The first example shows that error, then the two shortcut forms.

INSERT OR IGNORE

INSERT OR IGNORE silently skips any row that would violate a uniqueness constraint and carries on with the rest. It is handy for "make sure this exists" jobs such as seeding lookup values. The cost is that you learn nothing about what was skipped, except through changes().

SQL OR IGNORE keeps the old row

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

Four rows were offered but only python and go were new, so changes() reports 2 and the table holds four tags with ids 1 to 4. Without OR IGNORE the very first duplicate would have raised a UNIQUE constraint failed: tags.name error and, because a single statement is atomic, none of the four rows would have been inserted.

INSERT OR REPLACE and its pitfalls

INSERT OR REPLACE (also written REPLACE INTO) resolves a conflict by deleting the old row and then inserting the new one. That sounds like an update, but it is not, and the difference bites:

  • Columns you leave out are not preserved: the new row gets defaults, not the old values.
  • The old row is truly deleted, so it gets a new rowid if the table has no explicit primary key value, and foreign keys with ON DELETE actions can fire.
  • Delete triggers run only if recursive triggers are enabled, which is off by default.
SQL REPLACE loses the columns you omit

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

The nickname changed, but login_count dropped from 7 back to its default of 0, because the old row was thrown away and a fresh one written. If you only meant to change the nickname, use an upsert instead.

Treat OR REPLACE as "delete and insert". Use it only when the new row is meant to replace the old one completely, for example a cache table where every column is rewritten.

ON CONFLICT DO UPDATE (upsert)

SQLite 3.24 added the standard upsert clause. You add ON CONFLICT (column) DO UPDATE SET ... after the values. If the row is new it is inserted; if the named unique column collides, the existing row is updated in place and nothing is deleted. Inside the SET list, two things are available: the existing row's columns by their plain names, and the row you tried to insert through the special table name excluded.

SQL A counter that creates itself

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

/home ends with 3 views (one insert and two updates) and /about with 1. The last statement shows the same idea written with excluded.views, which is the value 1 from the attempted insert. Using excluded is more flexible: one statement can then upsert many rows, each carrying its own number.

Choosing which columns to overwrite

The conflict target (path) must name a column or column list that has a UNIQUE index or is the primary key. In SET, update only what should change. A common pattern is "last write wins, but only if newer", which adds a WHERE to the DO UPDATE:

SQL Upsert many rows, update only when newer

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

A1 is updated (the incoming date is newer), B2 is left alone (the incoming row is older, so the WHERE fails and the row is untouched), and C3 is simply inserted. Notice how the existing row is addressed by the table name (prices.updated_at) and the incoming one as excluded.

Two related options exist. ON CONFLICT DO NOTHING is the upsert-style equivalent of OR IGNORE, and it can name a specific constraint so that other kinds of violations still raise errors. When parsing an INSERT ... SELECT, add WHERE true to the SELECT so the parser does not mistake ON for a join clause.

RETURNING: get the written rows back

SQLite 3.35 added RETURNING for INSERT, UPDATE and DELETE. After the statement runs, you receive one result row per affected row, with whatever expressions you list. This avoids a second SELECT and a call to last_insert_rowid(), and it shows values the database produced: ids, defaults and computed columns.

SQL RETURNING with an upsert

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

The first statement returns the generated id 1 and the defaulted plan and visits. The second hits the conflict, updates the existing row and returns id 1 with visits 2: RETURNING reports the row after the update. The third returns two rows with ids 2 and 3, and shows that you can alias columns and use expressions.

A few rules to remember:

  • RETURNING sees the final row after the change and after DEFAULTs. It does not see changes made by triggers.
  • An INSERT ... ON CONFLICT DO NOTHING returns no row for the skipped input, so do not assume one result per input row.
  • You cannot use RETURNING on a virtual table, and aggregate or window functions are not allowed in it.
  • The order of returned rows is arbitrary unless you are inserting a single row.

In application code, statements with RETURNING are run like queries: you step through the result rows, even though the statement changes data. Run all the rows to completion so the change is finished.

Which one should you pick?

Goal Use
Add if missing, never touch existing rows ON CONFLICT DO NOTHING (or OR IGNORE)
Insert or modify selected columns, keep the rest ON CONFLICT (...) DO UPDATE
Replace the whole row, defaults and all OR REPLACE
Know the new id or the final values add RETURNING

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does INSERT OR REPLACE do when the new row collides with an existing one?

    REPLACE is delete-then-insert. Columns you omit go back to their defaults, which is why it can silently lose data.
  2. 2. In ON CONFLICT (sku) DO UPDATE SET price = excluded.price, what is excluded?

    excluded exposes the proposed row. Plain column names refer to the row already in the table.
  3. 3. An upsert writes ON CONFLICT (email) but email has no UNIQUE constraint or index. What happens?

    The conflict target must match a PRIMARY KEY or UNIQUE constraint or index, otherwise SQLite rejects the statement.
  4. 4. What does RETURNING give you after an UPDATE?

    RETURNING works for INSERT, UPDATE and DELETE and shows the final values (for DELETE, the deleted row).

Summary

  • A conflict means a UNIQUE or PRIMARY KEY violation; plain INSERT errors out, and the whole statement is rolled back.
  • OR IGNORE and ON CONFLICT DO NOTHING skip duplicates; changes() tells you how many rows were actually added.
  • OR REPLACE deletes the old row first, so omitted columns reset to defaults and delete side effects can occur.
  • ON CONFLICT (col) DO UPDATE SET ... updates in place; excluded.col is the proposed value, and an optional WHERE makes the update conditional.
  • RETURNING hands back the written rows, including generated ids and defaults, in one round trip.

Next: Transactions, making several changes succeed or fail together.