What is an UPSERT? | Interview Question

Q: What is an UPSERT?

**Target Audience:** Beginners who know INSERT and UPDATE and want to learn UPSERT from zero; candidates who need the one-minute answer and the engine differences. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15 (the same examples plus MERGE, and two real `psql` sessions for the race). MySQL and SQL Server facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A library keeps a card for every member. When someone walks in, the librarian does one of two things: if the member already has a card, she updates the "last visit" date on it; if not, she writes a new card. Simple, as long as there is

The problem

Question presented to candidate: "What is an UPSERT? Show me how you would write one, and tell me why it is better than checking whether the row exists first."

What a strong answer should cover:

Definition: insert a row, or update it if a row with the same key already exists, as one statement
Why not "SELECT, then INSERT or UPDATE": two sessions can both see no row and both insert; one fails with a unique violation or a write is lost
It needs a unique constraint or primary key to decide what "already exists" means; the conflict target must match one
Syntax by engine: PostgreSQL and SQLite INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col or DO NOTHING; MySQL INSERT ... ON DUPLICATE KEY UPDATE; SQL Server and Oracle MERGE; PostgreSQL also has MERGE since 15
Pitfalls: SQLite INSERT OR REPLACE deletes and re-inserts (other columns reset), duplicate keys within one statement, MERGE is not a race-free upsert

Code / implementation expected: Yes — a counter upsert with ON CONFLICT DO UPDATE, and DO NOTHING.

Schema used by the examples:

upsert

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know INSERT and UPDATE and want to learn UPSERT from zero; candidates who need the one-minute answer and the engine differences. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15 (the same examples plus MERGE, and two real psql sessions for the race). MySQL and SQL Server facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A library keeps a card for every member. When someone walks in, the librarian does one of two things: if the member already has a card,

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLCount page views: insert new pages, add to existing ones (ON CONFLICT DO UPDATE)

SQLInsert only if absent: ON CONFLICT DO NOTHING

SQLSQLite pitfall: INSERT OR REPLACE deletes the old row, so stock resets to its default

PostgreSQL 15+: sync products from a price feed with MERGE

MERGE INTO products AS p
USING price_feed AS f ON p.sku = f.sku
WHEN MATCHED AND p.price <> f.price THEN
  UPDATE SET price = f.price, updated_on = '2026-10-17'
WHEN NOT MATCHED THEN
  INSERT (sku, name, price, stock, updated_on)
  VALUES (f.sku, f.name, f.price, 0, '2026-10-17');

SELECT sku, name, price, stock, updated_on FROM products ORDER BY sku;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.