There's one operation real systems constantly need and that none of the three previous statements solves: "insert this row if it doesn't exist, and update it if it does".

Synchronising the catalogue with the file a supplier sends every week: some items are already in the system and only change price, others are new. Recording the stock received for a reference that may not even exist yet. Saving the rating of a review the customer may have written months ago. Registering a customer who may already have signed up. In all four cases, the answer to "INSERT or UPDATE?" is it depends on what's in the table, and you don't know until you look.

The solution everybody writes the first time —look and then decide— is wrong, and it's wrong in a way that never shows up in development and always shows up in production. This lesson starts by explaining why, and goes on to the two tools PostgreSQL offers to solve it in a single atomic statement: INSERT ... ON CONFLICT, its own solution since version 9.5, and MERGE, the SQL standard's, available since PostgreSQL 15.

1. The problem: insert or update depending on what's there
2. Why the naive solution is wrong
3. INSERT ... ON CONFLICT: the syntax
4. DO NOTHING and DO UPDATE SET
5. The conflict target and why it requires a unique constraint
6. The EXCLUDED pseudo-table and the accumulation pattern
7. WHERE in the DO UPDATE: updating only if something changes
8. RETURNING with an upsert, and how to tell an insert from an update
9. MERGE: the SQL standard's upsert
10. ON CONFLICT against MERGE
11. Support by engine
12. Three GreenStore cases
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

Huerta del Turia (supplier 1) sends an updated catalogue file every Monday. This week it brings five references:

Four are already in the catalogue (with different prices); the fifth is new. And not even all the existing ones change: the rice is still at €3.90 and €2.10.

With what you know so far, you'd have to split the file into two groups by hand and write an INSERT for one and an UPDATE for the other. It's tedious, and with five hundred references it's downright unworkable. What you need is a statement that decides for itself, row by row.

That's an UPSERT: update + insert.

Everybody's first idea, and the one most applications write:

It works perfectly… as long as you're the only person connected. As soon as there are two processes working at once, a race condition appears:

Both sessions look, both see it doesn't exist, both decide to insert. The first succeeds; the second blows up. And there's a worse variant: if there were no unique constraint, the second insertion would succeed and you'd end up with two duplicate rows and no error at all.

The interval between the SELECT and the INSERT may look tiny —milliseconds—, but on a system processing thousands of operations a minute that window gets crossed constantly. It isn't a rare case: it's a guaranteed one. The general rule behind it:

Checking and then acting in two separate statements is never safe against concurrency. Between the check and the action, the world may have changed.

The three ways of solving it, from worst to best:

Why the third option is safe. When PostgreSQL runs an INSERT ... ON CONFLICT, the check and the write happen inside the same operation, with the appropriate row and index lock: there's no window between "look" and "act". The complete mechanism —what gets locked, for how long and what the other sessions see— is module 9. Here it's enough to know that one statement is atomic and two aren't.

It reads literally: "insert this; if it clashes with the uniqueness of these columns, do nothing / or do this UPDATE instead".

Let's start with the case that needs nothing new, because GreenStore already has the constraint: customers.email is UNIQUE.

(The three examples in this section are run in order on the freshly reloaded database. Watch the ids: why they come out the way they do is explained at the end.)

Zero rows inserted, zero errors. The customer already existed with that email, so the statement simply did nothing.

Untouched: still in Valencia and with her original signup date.

DO NOTHING is ideal for rerunnable master data: development seeds, reference catalogues, initial loads. It turns a fragile script into an idempotent one (05-03).

The city has gone from Valencia to Alicante, and signup_date has not changed: only what appears in the SET gets updated. That's exactly what we want — a customer's original signup date shouldn't be rewritten just because their details get sent to us again.

And with a new email, the same statement inserts:

The same statement has inserted in one case and updated in the other, without you having had to decide anything. That's the upsert.

Why the id is 18 and not 16. GreenStore has 15 customers and its sequence was left at 15 after the script's setval. But an upsert that ends in a conflict also consumes a sequence value: PostgreSQL builds the complete row —evaluating every DEFAULT, nextval included— before checking the unique index. The two previous examples on Lucía took 16 and 17, and Aitor got 18.

It's consistent with what you saw in 05-02: sequences aren't undone. Practical consequence: in a table with frequent upserts, the ids have big gaps and they don't count rows. If that matters to you, review the type: an INTEGER runs out at 2,147,483,647, and with massive upserts that comes sooner than you'd think. BIGINT is the usual answer.

The ON CONFLICT (...) isn't decorative: it tells PostgreSQL which conflict to intercept. And it can only intercept violations of a UNIQUE, PRIMARY KEY or unique index constraint.

There are two ways to state it:

If you name columns that aren't covered by any unique constraint, it fails:

And this is a design limitation, not a whim. For PostgreSQL to be able to decide atomically that "it already exists", it needs a unique index to tell it so without scanning the table; without one it would have to search, and we'd be back to section 2's race window.

So to synchronise the catalogue by name you have to add the constraint:

It works because the catalogue's twenty names are all different. And along the way it illustrates something you'll see in depth in 05-06: adding a UNIQUE creates an index underneath, and that index is what makes the upsert possible (the structures and their cost, in module 8).

Now we're ready:

Price and cost updated; the stock is still 120 because it isn't in the SET. The file's 60 units have been ignored, which in this case is correct: they're units sent, not the total stock.

EXCLUDED is the key to the whole mechanism. It's a pseudo-table containing the row that was proposed for insertion and was rejected because of the conflict.

Inside the DO UPDATE SET two worlds coexist:

With the two of them you can write any merge rule:

That last one is the accumulation pattern, and it's probably the most valuable use of the upsert. Recording a goods receipt is exactly that: "if the product is already in the system, add the units to it; if not, create it with those units".

Five rows: four accumulated and one created. 120+60=180, 200+100=300, 300+200=500, 60+40=100, and the chickpeas come in with their 140 units and the id 21.

Notice one revealing detail: the prices of the four updated rows are still the old ones (€12.50, €3.90, €1.95, €4.95), because the SET only touches stock. Only the new row got the file's price. The upsert lets you control field by field what gets merged and what gets kept.

And the compulsory warning, brother of UPDATE ... FROM's (05-03):

⚠️ EXCLUDED doesn't accumulate across tuples of the same statement. If the file brought the same name twice, PostgreSQL would fail with ON CONFLICT DO UPDATE command cannot affect row a second time. It doesn't add the two together: it refuses. Deduplicate the source before you upsert.

That error is actually good news: the engine refuses to do something ambiguous rather than inventing a result. Compare it with UPDATE ... FROM, which in the same situation picks a match at random without warning you.

The DO UPDATE accepts a WHERE of its own, which decides whether the update is applied or discarded:

Four rows instead of five. The brown rice came in at €3.90 and €2.10, exactly what it already had, so the WHERE discarded its update and it doesn't even appear in the RETURNING.

Why that extra line is worth it:

Notice the use of IS DISTINCT FROM instead of <>. It's 04-03 all over again: if products.cost were NULL, the comparison products.cost <> EXCLUDED.cost would give UNKNOWN, the WHERE would discard it and the cost would never be updated. IS DISTINCT FROM treats two nulls as equal and a null against a value as different, which is what we need here. It's one of the places where that lesson pays its debt.

RETURNING works just as it does in INSERT and UPDATE: it returns the affected rows, whether inserted or updated. What it doesn't tell you directly is which was which.

There's a very widespread trick based on the system column xmax (the example runs on the freshly reloaded database):

Lucía was updated (false), Marisol was created (true) — with the id 17, because the failed attempt on Lucía took 16.

How it works. xmax is a hidden column PostgreSQL uses internally for version control: it holds the identifier of the transaction that deleted or locked the row. On a freshly inserted row it's 0; on one updated inside the upsert, it isn't.

⚠️ Use it with reservations. xmax is an implementation detail, not a documented interface. It can change between versions, and there are situations (rows locked by other transactions, a previous SELECT ... FOR UPDATE) in which a non-zero xmax doesn't mean what you think. It's perfectly good for debugging and for an internal report; don't build critical business logic on it.

And one limitation that does matter: with DO NOTHING, the conflicting rows don't appear in the RETURNING at all. You'll only see the ones that were genuinely inserted:

One of the two. If you also need to know what got ignored, DO NOTHING isn't going to tell you.

ON CONFLICT is a PostgreSQL extension. The SQL:2003 standard defines the MERGE statement for the same job, which PostgreSQL adopted in version 15.

The logic is a JOIN's: target and source are matched by the ON condition, and for each row the first WHEN clause that holds is executed.

The source can be a table, a query or a list of values. For GreenStore's case, the natural choice is a staging table with the contents of the supplier's file:

That COPY into a staging table followed by a merge towards the definitive table is the professional data integration pattern 05-02 announced.

And now the same case as section 7, solved with MERGE:

Four rows affected: three updates (olive oil, tomato and kombucha) and one insertion (chickpeas). The rice doesn't satisfy the WHEN MATCHED condition and, with no further applicable clauses, it stays as it is.

Notice the orange juice (17): it's from Huerta del Turia but it didn't come in the file, and it has been left untouched. Neither ON CONFLICT nor this MERGE does anything with target rows the source doesn't mention. If the business criterion were "anything not in the file gets discontinued", you'd need two operations… or a WHEN NOT MATCHED BY SOURCE clause, which PostgreSQL 16 still doesn't have (it arrived in 17).

Several conditions and DELETE. This MERGE synchronises the catalogue and also discontinues whatever the supplier sends at price 0:

Three different rules in one statement, evaluated in order: the first one that holds wins. With ON CONFLICT this can't be expressed; you'd need several statements.

That third limitation is counter-intuitive and worth remembering: the standard statement is less robust against concurrency than the proprietary extension, because ON CONFLICT leans directly on the unique index and MERGE doesn't require one.

When to use each:

• ON CONFLICT when the case is the classic "insert or update" over a unique key, when there's real concurrency, or when you need RETURNING. That's 90 % of cases.
• MERGE when you need several rules (update some, delete others, insert the rest), when the matching isn't on a unique key, when the source is a complex query, or when the code has to be portable to Oracle or SQL Server.

SQLite also offers INSERT OR REPLACE INTO ..., which a lot of people use believing it's an upsert. It isn't, and the difference is serious:

Applied to GreenStore it would be a silent catastrophe: an INSERT OR REPLACE on products would delete the row, and with it all its reviews would go in cascade (reviews.product_id is ON DELETE CASCADE). The "upsert" would have destroyed data in another table without ever mentioning it.

The rule: in SQLite use ON CONFLICT ... DO UPDATE, never INSERT OR REPLACE, unless delete-and-recreate is exactly what you want.

Solved in sections 7 and 9, both ways. It requires the uq_products_name constraint for the ON CONFLICT version; the MERGE would work just the same without it.

Solved in section 6 with the accumulation pattern SET stock = products.stock + EXCLUDED.stock. It's the case where the upsert shines: a single statement processes an entire delivery note, whether it creates new references or adds to existing ones.

A more complete version, which also updates the purchase cost and records the added date only on the new references:

Four accumulations and one new entry. And look at the added_date column: the four existing ones keep theirs (2025) and only the new one gets today's, because added_date doesn't appear in the SET. That asymmetry —some fields get merged and others only get filled in on creation— is exactly what makes DO UPDATE useful compared with a REPLACE.

Notice as well that the source is a SELECT, not a VALUES. INSERT ... SELECT ... ON CONFLICT combines 05-02's material with this lesson's, and it's the usual way of processing an entire staging table.

A customer rates a product they'd already reviewed. The business rule: one review per customer and product, and the latest one replaces the previous.

Today reviews doesn't prevent duplicates, so first that rule has to be declared:

It works because the twelve current reviews all have different (product_id, customer_id) pairs. If there were duplicates, the ALTER TABLE would fail — which is exactly what it should do.

Now the upsert. Customer 6 (Pau Llorens Vidal) had given the Kombucha a 2; they've changed the recipe and he wants to raise it to 4:

The same row (id 6), new content. And with a customer who has never reviewed that product, the same statement creates the review:

The id is 14 and not 13 for the same reason as in section 4: the previous upsert, which ended in a DO UPDATE, had already taken 13 from the sequence.

And the effect on the product's rating:

From a lonely 2 to an average of 4.50 with two reviews. The Kombucha stops being the catalogue's worst product.

Remember to undo the examples. This lesson's two constraints (uq_products_name and uq_reviews_product_customer) are not part of the course schema. Remove them with ALTER TABLE ... DROP CONSTRAINT ... or, more simply, reload greenstore.sql. The full syntax of ALTER TABLE is the next lesson.

• Solving the upsert with SELECT + INSERT/UPDATE. A guaranteed race condition under concurrency. One statement is atomic; two aren't.
• Using ON CONFLICT over columns with no unique constraint. there is no unique or exclusion constraint matching the ON CONFLICT specification. The unique index is the mechanism, not a bureaucratic requirement.
• Forgetting the EXCLUDED. prefix in the DO UPDATE. SET price = price is a circular assignment: the row stays as it was and no error is raised.
• Bringing the same key value twice in the same statement. cannot affect row a second time. Deduplicate the source first.
• Comparing with <> instead of IS DISTINCT FROM in the DO UPDATE's WHERE. With a NULL involved the comparison gives UNKNOWN and the row is never updated (04-03).
• Expecting DO NOTHING to tell you what was ignored. It doesn't appear in the RETURNING. If you need to know, use DO UPDATE with a WHERE.
• Building business logic on xmax = 0. It's an implementation detail. Good for debugging; not for invoicing.
• Believing the upsert also updates what the source doesn't mention. It doesn't. Target rows absent from the file are left untouched.
• Using INSERT OR REPLACE in SQLite believing it's an upsert. It deletes and recreates: it loses columns, it changes the rowid and it fires the delete cascades.
• Assuming MERGE is safe against concurrency. It isn't in PostgreSQL 16: it can fail with a duplicate key. ON CONFLICT is.
• Tip: for a delivery note or a file, load into a staging table and merge from there. COPY + INSERT ... SELECT ... ON CONFLICT (or MERGE) is the professional integration pattern.
• Tip: decide field by field what gets merged. price yes, added_date no, stock accumulating. That granularity is the entire value of DO UPDATE.
• Tip: always put the WHERE ... IS DISTINCT FROM in the DO UPDATE. Fewer writes, fewer locks, honest auditing and a RETURNING that tells the truth.

Work on the freshly reloaded database, inside BEGIN … ROLLBACK.

GreenStore receives a file of customer additions and updates from a marketing campaign:

Write a single statement that adds the ones that don't exist and updates the city of the ones that do, with these rules:

1. signup_date must be 2026-03-02 for the new ones and must not be modified on the existing ones.
2. The city should only be updated if it genuinely changes.
3. The result must indicate, per row, whether it was an addition or an update.

Then answer: how many rows does the RETURNING give back and why?

Solve the same case as exercise 1 with MERGE, first loading the data into a temporary table campaign_customers. Then compare the two solutions by answering these questions:

1. What's the difference in psql's output?
2. Which of the two is safe if two processes run the campaign at the same time?
3. Which would you write if you also had to deactivate the customers not appearing in the file? Can it be done in PostgreSQL 16?

A colleague wants to synchronise the catalogue and writes this:

It has three distinct errors. Find them, explain the symptom of each one and rewrite the statement correctly, stating what would have to be declared in the schema for it to work and what result it would give.

Solution 1

Four rows, and it's no accident that all four come out:

If the file had brought Lucía with her current city (Valencia), the WHERE ... IS DISTINCT FROM would have discarded that update and the RETURNING would have given back three rows.

The brief's three decisions:

1. signup_date outside the SET. It appears in the INSERT (for the new ones) but not in the DO UPDATE, so the existing ones keep theirs: 2025-01-10 and 2025-03-21. It's the same principle as added_date in 12.2.
2. WHERE customers.city IS DISTINCT FROM EXCLUDED.city, not <>: if any customer had a NULL city (the column allows it), <> would give UNKNOWN and it would never be updated.
3. (xmax = 0) AS is_new, with section 8's reservation: it's good for the campaign report, not for critical logic.

Solution 2

An identical result in content, with one revealing detail in the ids: here they're 16 and 17, not 18 and 19 as with ON CONFLICT. The reason is that MERGE only evaluates the DEFAULTs of the rows it actually inserts, while the upsert evaluates them on the ones that end in a conflict too. MERGE wastes fewer sequence values. (Even so, don't count on consecutive ids with either of them: sequences have gaps by design.)

The three answers:

1. The output. MERGE 4 against INSERT 0 4, and above all: PostgreSQL 16's MERGE doesn't accept RETURNING, so you have to query afterwards to see what happened — and that query can no longer distinguish additions from updates. It's the most notable loss when switching statements.

2. Concurrency. The safe one is ON CONFLICT. If two processes launch the campaign at once, the MERGE can fail with duplicate key value violates unique constraint "customers_email_key", because its matching doesn't lean on the unique index in the same way. ON CONFLICT is designed precisely for that scenario.

3. Deactivating the absent ones. That would be the job of a WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ... clause, which PostgreSQL 16 doesn't have (it arrived in 17). In 16 you have to do it in two statements inside the same transaction: the MERGE (or the upsert) and then an UPDATE ... WHERE email NOT IN (SELECT email FROM campaign_customers). Careful with that NOT IN and nulls: 04-02 warned about it, and here campaign_customers.email is a PRIMARY KEY, so it's safe. On top of that, customers has no deactivation column, so in GreenStore the operation wouldn't even be expressible without a prior ALTER TABLE — next lesson.

Solution 3

The three errors:

There's a fourth detail that isn't a syntax error but is a matter of judgement: SET stock = stock + EXCLUDED.stock works because an unqualified stock resolves to the existing row, but it's ambiguous to read. Always write products.stock + EXCLUDED.stock.

What the schema would need. To match by name you need section 5's constraint:

The corrected version, with the source deduplicated (we keep the honey's last shipment, the one at €10.50, and we add the two quantities: 40 + 25 = 65):

Both products already existed (ids 3 and 4, from BioSierra Ibérica): the honey goes from €9.75 to €10.50 and from 80 to 145 units (80 + 65); the pasta goes from €2.80 to €2.95 and from 150 to 230 units (150 + 80).

And the exercise's underlying lesson: of the three errors, two produce no message at all. The ON CONFLICT (id) would have duplicated the catalogue in silence and the SET price = price would have left the prices untouched without anybody noticing. Only the third —the duplicate in the source— triggers a visible error. A badly written upsert fails quietly far more often than noisily, and that's why you have to check the RETURNING row by row the first time it goes into production.

The upsert solves the operation that was missing:

• The problem: "insert if it doesn't exist, update if it does", ubiquitous in catalogue synchronisation, goods receipt, customer signup and ratings.
• The naive solution is wrong: SELECT and then decide opens a race condition between the check and the write. Two sessions see "it doesn't exist" and both insert. One statement is atomic; two aren't.
• INSERT ... ON CONFLICT, with its two actions: DO NOTHING (ideal for rerunnable master data, INSERT 0 0 with no error) and DO UPDATE SET (the upsert proper).
• The conflict target, by column or by ON CONSTRAINT, and why it requires a unique constraint: without the index, PostgreSQL couldn't decide atomically and the race window would come back.
• EXCLUDED, the pseudo-table with the proposed row, against table.column with the existing row. Every pattern comes out of those two: keep the new one, keep the old one, GREATEST, and above all accumulate (stock = products.stock + EXCLUDED.stock). And its limit: the same key value twice in one statement fails, you have to deduplicate the source.
• WHERE in the DO UPDATE with IS DISTINCT FROM (04-03) so as not to write when nothing changes: fewer writes, fewer locks, honest auditing.
• RETURNING with an upsert, and the xmax = 0 trick to tell an addition from an update, with its reservations.
• MERGE, the SQL:2003 standard available since PostgreSQL 15: WHEN MATCHED / WHEN NOT MATCHED, several conditions evaluated in order and the ability to delete. With its three limitations in PostgreSQL 16: no RETURNING, no WHEN NOT MATCHED BY SOURCE, and not immune to concurrency.
• When to use each: ON CONFLICT for the classic case with real concurrency; MERGE for multiple rules, matchings without a unique key and portability.
• The support by engine, and the warning about SQLite's INSERT OR REPLACE, which deletes and recreates the row: it loses columns, it changes the rowid and it fires the delete cascades.

With this you've closed the DML: you know how to create, read, insert, modify, delete and merge. All of it over a schema that has so far been immutable: the same one you created in 05-01 and haven't touched since. But schemas change. A new column is needed, a type turns out to be too small, a constraint arrives late, a name turns out to be a mistake. In the module's last lesson, Changing the Schema: ALTER TABLE and Safe Migrations, you'll learn all the ALTER TABLE operations and —what really separates a harmless migration from a production outage— which ones lock the table and which don't, the expand/contract pattern for changing a schema without stopping the service, and why no structural change should ever be typed by hand into a production console.