PostgreSQL UPSERT with ON CONFLICT

Schemas and Data / Guide 9 of 47 / Intermediate

• OSUbuntu 26.04 LTS (resolute)
• PostgreSQL18.6-0ubuntu0.26.04.1
• Server timezoneEtc/UTC
• TimeAbout 13 min
• Reviewed27 August 2026

Written against the versions above. `ON CONFLICT` needs a unique constraint or index to conflict against. Naming the column, as here, uses the constraint covering it.

Before you start

• The customers table with its unique email constraint.

1. 1

   Insert a row that already exists, and update it instead

   The email ada@example.com is already taken - the constraints guide proved it with a duplicate-key error. This statement expects that.

   ON CONFLICT (email) names what to conflict on: the unique constraint covering that column. DO UPDATE SET credit = EXCLUDED.credit then runs, where EXCLUDED is a pseudo-table holding the row that was *proposed* - the one the insert would have added. So EXCLUDED.credit is the new 500, and the bare credit would be the existing value.

   RETURNING confirms the outcome: id 1, the existing row, now carrying 500.00. Nothing was inserted; one row was updated.

   The value of this is atomicity. Checking whether a row exists and then inserting or updating is two statements with a gap between them, and under concurrency that gap produces duplicate-key errors. ON CONFLICT closes it in the server.

   $psql -d appdb -c "INSERT INTO customers (name, email, credit) VALUES ('Ada Lovelace','ada@example.com',500) ON CONFLICT (email) DO UPDATE SET credit = EXCLUDED.credit RETURNING id, name, credit" id |     name     | credit----+--------------+--------  1 | Ada Lovelace | 500.00(1 row) INSERT 0 1

   Expected resultOne row returned - id 1, Ada Lovelace, credit 500.00.

   Success conditionYou can insert-or-update in one atomic statement.

2. 2

   Ignore the conflict instead of acting on it

   DO NOTHING is the other half of ON CONFLICT, and the two calls here show the behaviour that catches people.

   The conflicting insert returns (0 rows) and reports INSERT 0 0. Nothing was inserted, nothing was updated, and RETURNING gave back nothing at all - there is no row to return. Code that assumes a RETURNING clause always yields a row will break on exactly this case, and only when a conflict actually occurs.

   The second insert has no conflict, so it inserts normally and returns id 8.

   DO NOTHING is right for idempotent loads - replaying a feed, seeding reference data - where an existing row is fine and should be left alone.

   $psql -d appdb -c "INSERT INTO customers (name, email) VALUES ('Ada Third','ada@example.com') ON CONFLICT (email) DO NOTHING RETURNING id, name" id | name----+------(0 rows) INSERT 0 0$psql -d appdb -c "INSERT INTO customers (name, email) VALUES ('Brand New','new@example.com') ON CONFLICT (email) DO NOTHING RETURNING id, name" id |   name----+-----------  8 | Brand New(1 row) INSERT 0 1

   Expected result(0 rows) and INSERT 0 0 for the conflict; one row and INSERT 0 1 for the new email.

   Success conditionYou know DO NOTHING returns no row, and why that matters.

3. 3

   Find out which one actually happened

   RETURNING alone cannot tell you whether a row was inserted or updated - both return a row that looks the same.

   (xmax <> 0) AS was_update answers it. xmax is a system column holding the transaction that deleted or superseded a row version; on a freshly inserted row it is 0, and on one replaced by the upsert it is not.

   The conflicting statement returns was_update: t; the genuinely new row returns id 10 with the opposite. This is a documented idiom rather than a supported API - it relies on an internal column - but it is the standard answer and it is stable in practice.

   $psql -d appdb -c "INSERT INTO customers (name, email, credit) VALUES ('Ada Lovelace','ada@example.com',600) ON CONFLICT (email) DO UPDATE SET credit = EXCLUDED.credit RETURNING id, name, credit, (xmax <> 0) AS was_update" id |     name     | credit | was_update----+--------------+--------+------------  1 | Ada Lovelace | 600.00 | t(1 row) INSERT 0 1

   Expected resultt for the row that already existed, and a new id for the one that did not.

   Success conditionYou can distinguish an insert from an update in the same statement.

4. 4

   Guard the update so it only moves one way

   DO UPDATE takes its own WHERE, evaluated after the conflict is detected, and this is where upsert becomes genuinely powerful.

   The clause is WHERE customers.credit < EXCLUDED.credit - only accept the incoming value if it is larger. The statement offers 5 against a stored 600, so the condition is false: (0 rows), INSERT 0 0, and the follow-up SELECT confirms the value is still 600.

   Note the two qualifiers. customers. is the existing row, EXCLUDED. is the proposed one, and mixing them up silently inverts the logic.

   This shape - accept only if newer, only if larger, only if the status is still pending - is how out-of-order message delivery is made safe without a transaction and a read.

   $psql -d appdb -c "INSERT INTO customers (name, email, credit) VALUES ('Ada Lovelace','ada@example.com',5) ON CONFLICT (email) DO UPDATE SET credit = EXCLUDED.credit WHERE customers.credit < EXCLUDED.credit RETURNING id, credit" id | credit----+--------(0 rows) INSERT 0 0$psql -d appdb -c "SELECT id, name, credit FROM customers WHERE email='ada@example.com'" id |     name     | credit----+--------------+--------  1 | Ada Lovelace | 600.00(1 row)

   Expected result(0 rows), then the stored credit still reading 600.00.

   Success conditionYou can write an upsert that refuses to go backwards.

5. 5

   Use RETURNING on a delete

   RETURNING is not an insert feature - it works on UPDATE and DELETE too, and on a delete it is the difference between hoping and knowing.

   Two rows returned with their ids and names, then DELETE 2. You have a record of exactly what was removed, in the same statement that removed it, with no chance of the data changing between a SELECT and the DELETE.

   For any destructive statement run by hand, this is the habit worth having: it costs nothing and it is the only artefact you will have afterwards.

   $psql -d appdb -c "DELETE FROM customers WHERE email IN ('new@example.com','fresh@example.com') RETURNING id, name" id |   name----+-----------  8 | Brand New 10 | Fresh Row(2 rows) DELETE 2$psql -d appdb -c "SELECT count(*) AS remaining FROM customers" remaining-----------         3(1 row)

   Expected resultBoth deleted rows listed, DELETE 2, and three customers remaining.

   Success conditionYou can record what a destructive statement removed as it removes it.

Troubleshooting

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

  Why: No unique constraint covers the named columns.

  Fix:Create one. ON CONFLICT is implemented by the index, so it cannot work without.

• Wanting to ignore duplicates rather than update them.

  Why: DO UPDATE is not the only option.

  Fix:ON CONFLICT DO NOTHING. Note it returns no row, so RETURNING yields nothing when the conflict fires.

• Needing to know whether a row was inserted or updated.

  Why: RETURNING alone does not say which happened.

  Fix:RETURNING *, (xmax = 0) AS was_inserted - a documented idiom, though it relies on an internal column.

Official sources

• PostgreSQL 18 Documentation - Data Types
• PostgreSQL 18 Documentation - Date/Time Types
• PostgreSQL 18 Documentation - Constraints
• PostgreSQL 18 Documentation - INSERT ... ON CONFLICT
• PostgreSQL 18 Documentation - JSON Types and Functions
• PostgreSQL 18 Documentation - Arrays