25 UPSERT (ON CONFLICT)

“Upsert” is the operation that means “insert if it does not exist; otherwise update.” PostgreSQL spells it as INSERT ... ON CONFLICT ... DO UPDATE (or DO NOTHING). It is atomic, race-free, and one of the most-used patterns in production code. Before this clause existed, applications had to do a SELECT-then-INSERT-then-UPDATE dance that was either slow or wrong under concurrency.

What you’ll learn

• INSERT ... ON CONFLICT DO NOTHING to skip duplicates
• INSERT ... ON CONFLICT (col) DO UPDATE SET ... to merge new and old values
• Referencing the proposed values via the EXCLUDED virtual table
• The conflict target: column, constraint name, or expression
• RETURNING with ON CONFLICT and when it gives you the row

Example

-- Setup a table with a unique key
DROP TABLE IF EXISTS visitors_example;
CREATE TABLE visitors_example (
  email      text PRIMARY KEY,
  name       text NOT NULL,
  visits     integer NOT NULL DEFAULT 1,
  last_seen  timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice');

-- DO NOTHING: insert if absent, otherwise skip silently
INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice2')
ON CONFLICT (email) DO NOTHING;

-- DO UPDATE: on conflict, merge new values into the existing row
INSERT INTO visitors_example (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
  SET visits    = visitors_example.visits + 1,
      last_seen = current_timestamp;

-- EXCLUDED refers to the row that was *proposed* by the INSERT
INSERT INTO visitors_example (email, name) VALUES ('bob@example.com', 'Bob')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,             -- use the new name
      visits = visitors_example.visits + 1
RETURNING *;

-- Conflict on a partial / expression unique index works too
-- CREATE UNIQUE INDEX ON visitors_example (lower(email));
-- INSERT ... ON CONFLICT ((lower(email))) DO UPDATE ...

SELECT * FROM visitors_example ORDER BY email;

The conflict target — the parenthesized clause after ON CONFLICT — tells PostgreSQL which unique constraint or index defines the conflict. (email) matches the unique key on email. You can also name a constraint: ON CONFLICT ON CONSTRAINT visitors_example_pkey DO UPDATE .... For expression indexes, repeat the expression: ON CONFLICT ((lower(email))). A partial index can also require a matching predicate after the conflict target.

EXCLUDED is the virtual table representing the row your INSERT proposed. visitors_example (or whatever the actual table is called) refers to the existing row. Combining them lets you express increments (SET visits = visitors_example.visits + 1), merges (SET name = COALESCE(EXCLUDED.name, visitors_example.name)), and so on.

RETURNING with ON CONFLICT DO UPDATE returns every affected row (inserted or updated). DO NOTHING only returns truly inserted rows — if a conflict was skipped, you get nothing back.

Run the example

To run:

$ psql -f source/upsert.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 0       -- DO NOTHING skipped the duplicate
INSERT 0 1       -- DO UPDATE merged
INSERT 0 1
       email       | name  | visits |           last_seen
-------------------+-------+--------+-------------------------------
 bob@example.com   | Bob   |      1 | 2024-xx-xx xx:xx:xx.xxxxxx+00
(1 row)

       email       | name  | visits |           last_seen
-------------------+-------+--------+-------------------------------
 alice@example.com | Alice |      2 | 2024-xx-xx xx:xx:xx.xxxxxx+00
 bob@example.com   | Bob   |      1 | 2024-xx-xx xx:xx:xx.xxxxxx+00
(2 rows)

Common pitfalls

• ON CONFLICT requires a matching unique constraint or index to be defined. Without one, PostgreSQL has no way to know which row would conflict and raises an error.
• The conflict target must exactly match a constraint or index. ON CONFLICT (lower(email)) only works if you have a unique expression index on lower(email).
• DO NOTHING is fine when you really mean “best-effort insert.” It silently swallows real bugs if you actually expected the row to be inserted.
• RETURNING after DO NOTHING does not return a row for the skipped insert. If you need “the row, whether you inserted it or it already existed,” use DO UPDATE with a no-op SET like SET email = EXCLUDED.email so a row is always returned.

Downloads

SQL file: upsert.sql · All lesson files · Setup guide