Postgres by Example: 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

-- 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_pkey DO UPDATE .... For partial or expression indexes, repeat the index's expression: ON CONFLICT ((lower(email))).

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.

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.

Tip: From PostgreSQL 15 onwards, the SQL-standard MERGE statement is also available — more flexible (multiple matched / not-matched branches) but more verbose. INSERT ... ON CONFLICT is still the right tool for simple upserts.

Try it: Run the second insert (DO NOTHING) twice — Alice's visit count should not increase. Then run the third (DO UPDATE) twice — it should each time. Add RETURNING * to each and observe which return rows.

Source: upsert.sql

Next: INNER and LEFT JOIN

Home: Postgres by Example