PostgreSQL has no UPSERT keyword. You write INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. When the new row would break a unique constraint on that column, PostgreSQL updates the existing row instead. DO NOTHING skips it.
A-100 already existed, so its price changed to 12.5. B-200 was new and was inserted. One statement handles both, and it stays correct when other sessions insert the same keys at once.
Insert or update one row
The statement has three parts:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- The conflict target names the columns of a unique index or constraint, such as the primary key. It must match one exactly, or PostgreSQL raises "there is no unique or exclusion constraint matching the ON CONFLICT specification". You can name the constraint instead:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDis the row you tried to insert.EXCLUDED.priceis the new price, andproducts.priceis the price already stored.DO UPDATE SETchanges only the columns you list. Other columns of the existing row keep their values.
A unique index with a WHERE clause (a partial index) works as a target too, if you repeat its condition: ON CONFLICT (email) WHERE deleted_at IS NULL.
Skip duplicates with DO NOTHING
DO NOTHING inserts the rows that are new and drops the ones that collide, without an error. Without a conflict target it skips a row that breaks any unique constraint.
RETURNING lists only lin@example.com. A skipped row is not returned, so DO NOTHING ... RETURNING shows which rows were new.
Add to a counter
To increase a stored value instead of replacing it, add EXCLUDED to the current value. Qualify the current value with the table name. Inside DO UPDATE, a bare views matches both the table and EXCLUDED, and PostgreSQL stops with "column reference "views" is ambiguous".
The first statement inserts the row with 1 and the next two add to it, so /home ends at 3.
Update only when the value changed
A WHERE after DO UPDATE SET decides, per row, whether the update runs. When it is false, the row is left as it is and counts as neither inserted nor updated. Use it to keep only the newest data, or to avoid writing a new row version when nothing changed.
A-100 keeps 10 because the incoming row is older. B-200 takes 25. To skip updates that would write the same values, use WHERE prices.price IS DISTINCT FROM EXCLUDED.price, which also handles NULL.
Tell inserted rows from updated ones
RETURNING works on an upsert as on any insert. To tell the two cases apart, read the system column xmax, which is 0 for a row this statement inserted:
A-100 comes back with 8 and inserted false, C-300 with 7 and true. xmax is an internal detail, not a documented API, so treat it as a convenience. The RETURNING page covers the clause in full.
Errors you will meet
- "ON CONFLICT DO UPDATE command cannot affect row a second time": the same key appears twice in one statement's
VALUESorSELECT. Remove duplicates first, for example withSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": qualify the column with the table name in
SETexpressions andWHERE, as above. - Gaps in identity or serial ids: an insert that turns into an update has already taken a value from the sequence, so ids skip numbers. That is expected.
MERGE (PostgreSQL 15 and later) can also insert or update, but it does not guard against two sessions inserting the same key at once. For upserts by key, use ON CONFLICT. The official INSERT reference lists every option.
Frequently Asked Questions
Does PostgreSQL have an UPSERT statement?
There is no UPSERT keyword. PostgreSQL 9.5 added INSERT ... ON CONFLICT, which inserts a row or, when it would break a unique constraint, updates the existing row (DO UPDATE) or skips it (DO NOTHING).
Is INSERT ... ON CONFLICT safe with concurrent inserts?
Yes. Each proposed row ends in either an insert or an update, even when other sessions insert the same key at the same moment. A SELECT followed by an INSERT or UPDATE in application code does not give that guarantee.
Should I use MERGE or ON CONFLICT for an upsert?
Use ON CONFLICT for insert-or-update by key. MERGE (PostgreSQL 15 and later) handles more cases, such as deleting rows that match. Under concurrent inserts of the same key it can fail with a unique violation, which ON CONFLICT avoids.
Why do I get "there is no unique or exclusion constraint matching the ON CONFLICT specification"?
The columns in ON CONFLICT (...) must match a unique index or constraint exactly. Add a UNIQUE constraint or unique index on those columns, or name the columns of one that already exists.