How to Upsert in PostgreSQL (INSERT ... ON CONFLICT)
Last updated August 26, 2026 · By the SaturnSQL team
INSERT ... ON CONFLICT (key) DO UPDATE inserts a row or updates the existing one when a unique constraint matches. EXCLUDED refers to the row you tried to insert.
INSERT INTO customers (id, email, updated_at)
VALUES (101, '[email protected]', now())
ON CONFLICT (id) DO UPDATE SET
email = EXCLUDED.email,
updated_at = now();Insert-if-missing only
INSERT INTO customers (id, email)
VALUES (101, '[email protected]')
ON CONFLICT (id) DO NOTHING;The conflict target must match a unique index or constraint. Postgres 15+ also offers MERGE for more complex conditional logic.
Update only when something actually changed
A plain DO UPDATE writes a new row version even when the values are identical, which bloats the table and wakes up every trigger. Adding a WHERE clause to the DO UPDATE skips the no-op writes. Note that the row is still locked either way, so a hot key can serialise concurrent upserts.
INSERT INTO orders (id, total)
VALUES (1, 49.90)
ON CONFLICT (id) DO UPDATE
SET total = EXCLUDED.total
WHERE orders.total IS DISTINCT FROM EXCLUDED.total;Common errors
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
ON CONFLICT (col) needs a unique index or constraint on exactly those columns to detect the conflict. A plain index, or a unique index on a different set of columns, does not count.
Create the unique constraint the upsert should key on, then rerun. If duplicates already exist, the index build fails and you have to clean them up first.
ALTER TABLE orders ADD CONSTRAINT orders_external_id_key UNIQUE (external_id);
INSERT INTO orders (external_id, total)
VALUES ('abc', 49.90)
ON CONFLICT (external_id) DO UPDATE SET total = EXCLUDED.total;ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
The batch you are inserting contains the same key twice. Postgres will not let one statement update the same row twice, because the result would depend on row order.
Deduplicate inside the statement and decide explicitly which row wins.
INSERT INTO orders (id, total)
SELECT DISTINCT ON (id) id, total
FROM incoming
ORDER BY id, updated_at DESC
ON CONFLICT (id) DO UPDATE SET total = EXCLUDED.total;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free