Incident brief
ON CONFLICT DO UPDATE requires inference specification or constraint name
INSERT ... ON CONFLICT DO UPDATE was written without telling PostgreSQL which unique index or constraint should decide the conflict. DO UPDATE always needs an explicit arbiter, a column list or constraint name. (Bare ON CONFLICT DO NOTHING can sometimes omit it when only one unique index exists.)
What lands in your log
ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
In 10 seconds
- What triggers it
- Create a table with a primary key and a separate UNIQUE column (for example email).
- Fix
- Name the conflict target: ON CONFLICT (email) DO UPDATE SET ...
- Proof
- Reproduced on PostgreSQL 16.14 → Bare ON CONFLICT DO UPDATE failed with SQLSTATE 42601 and HINT 'For example, ON CONFLICT (column_name).'. The same INSERT with ON CONFLICT (email) DO UPDATE succeeded and updated the existing row.
Fix
What to do right now
Application-level steps for this error.
- Name the conflict target: ON CONFLICT (email) DO UPDATE SET ...
- Or name the unique constraint: ON CONFLICT ON CONSTRAINT table_email_key DO UPDATE SET ...
- If you only want to skip duplicates, ON CONFLICT DO NOTHING is allowed without an inference target when a single unique index can be chosen, but DO UPDATE is never allowed without one.
- Do not remove the unique constraint to silence the error; fix the ON CONFLICT clause.
-- Tell PostgreSQL which unique key arbitrates the upsert
INSERT INTO upsert_lab (id, email, name) VALUES (2, 'a@ex.com', 'B')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *;
-- Equivalent: name the unique constraint
INSERT INTO upsert_lab (id, email, name) VALUES (3, 'a@ex.com', 'C')
ON CONFLICT ON CONSTRAINT upsert_lab_email_key DO UPDATE SET name = EXCLUDED.name
RETURNING *;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
ON CONFLICT DO UPDATE needs a conflict target. List the target table's usable unique indexes/constraints and match the application's intended business key.
Unique arbiters available on the target table
Replace the table literal. Partial indexes require the matching predicate in the ON CONFLICT target.
SELECT i.indexrelid::regclass AS index_name,
i.indisprimary,
pg_get_indexdef(i.indexrelid) AS index_definition,
pg_get_expr(i.indpred, i.indrelid) AS predicate
FROM pg_index i
WHERE i.indrelid = to_regclass('<schema.target_table>')
AND i.indisunique
ORDER BY i.indisprimary DESC, index_name::text;All unique constraints for comparison
Use the constraint name with ON CONFLICT ON CONSTRAINT when that is clearer than column inference.
SELECT conrelid::regclass AS table_name, conname,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype IN ('p','u','x')
ORDER BY conrelid::regclass::text, conname
LIMIT 100;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 16 Documentation, INSERT, ON CONFLICT Clause
The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken.Read the full section on postgresql.org →
Broken upsert (no inference target)
DO UPDATE must know which unique index is the arbiter. With both a primary key and a unique email, PostgreSQL refuses to guess, it raises 42601 at parse/transform time before any row is written.Fixed upsert (column inference)
ON CONFLICT (email) selects the unique index on email. The conflicting insert becomes an update of the existing row (id=1); RETURNING shows name flipped to B. SQLSTATE 23505 never appears because the conflict was handled.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Create a table with a primary key and a separate UNIQUE column (for example email).
- 2Insert one row.
- 3Run INSERT ... ON CONFLICT DO UPDATE SET ... without (columns) and without ON CONSTRAINT.
- 4PostgreSQL rejects the statement with SQLSTATE 42601 and this message, plus a HINT naming ON CONFLICT (column_name).
An upsert was written as ON CONFLICT DO UPDATE without naming which unique key should win, common when translating MySQL ON DUPLICATE KEY UPDATE habits.
DROP TABLE IF EXISTS upsert_lab;
CREATE TABLE upsert_lab (
id integer PRIMARY KEY,
email text UNIQUE,
name text
);
INSERT INTO upsert_lab VALUES (1, 'a@ex.com', 'A');INSERT INTO upsert_lab VALUES (2, 'a@ex.com', 'B')
ON CONFLICT DO UPDATE SET name = EXCLUDED.name;INSERT INTO upsert_lab VALUES (2, 'a@ex.com', 'B')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *;What PostgreSQL actually returned
DROP TABLE
CREATE TABLE
INSERT 0 1ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 2: ON CONFLICT DO UPDATE SET name = EXCLUDED.name;
^
HINT: For example, ON CONFLICT (column_name). id | email | name
----+----------+------
1 | a@ex.com | B
(1 row)ON CONFLICT ON CONSTRAINT <name> is an equivalent arbiter to ON CONFLICT (column_list) for the same unique constraint.
Without this
Column-list form: ON CONFLICT (email) DO UPDATE
With this, tested
Constraint form: ON CONFLICT ON CONSTRAINT upsert_lab_email_key DO UPDATE
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Understand the concept
Related errors
Verification
- Last verified
- 2026-08-15 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.