In PostgreSQL's INSERT ... ON CONFLICT, how is the arbiter index chosen, and when would you use ON CONSTRAINT instead of a column list?
answer
- inference, not a hint
- exactly the listed columns
- partial index needs its predicate
- only names in pg_constraint
basics
~20 sPostgreSQL infers arbiters: every unique index whose key is exactly the listed columns or expressions, with a matching WHERE for partial ones. ON CONSTRAINT names a primary-key, unique or exclusion constraint directly and cannot name a bare unique index.
solid answer
~40 sWith a column list, PostgreSQL performs **unique index inference**: every valid unique index whose key is exactly those columns or expressions, in any order, becomes an arbiter. An expression index needs the expression itself, such as `(supplier_id, lower(supplier_sku))`, and a partial unique index needs its predicate repeated as `ON CONFLICT (...) WHERE ...`. If nothing matches, the statement fails with `42P10`, 'there is no unique or exclusion constraint matching the ON CONFLICT specification'. `ON CONFLICT ON CONSTRAINT name` looks the name up in `pg_constraint`, so it works for a primary key, a `UNIQUE` constraint or, with `DO NOTHING`, an exclusion constraint, but a unique index made with `CREATE UNIQUE INDEX` fails with `42704`. The manual prefers inference because it survives swapping an index for an equivalent one.
code
sql · 13 linesCREATE UNIQUE INDEX catalog_product_sku_ci
ON catalog_product (supplier_id, lower(supplier_sku));
-- Inferred: the target repeats the indexed expression
INSERT INTO catalog_product (supplier_id, supplier_sku, title, price, stock)
VALUES (42, 'acme-1001', 'Cordless drill', 84.50, 30)
ON CONFLICT (supplier_id, lower(supplier_sku)) DO UPDATE
SET price = EXCLUDED.price, stock = EXCLUDED.stock;
-- Fails with 42704: an index is not a constraint
INSERT INTO catalog_product (supplier_id, supplier_sku, title, price, stock)
VALUES (42, 'acme-1001', 'Cordless drill', 84.50, 30)
ON CONFLICT ON CONSTRAINT catalog_product_sku_ci DO NOTHING;go deeper
Know that the column list must match a real unique index or constraint, otherwise the upsert errors instead of falling back.
Explain inference: exact column set in any order, expressions for expression indexes, the WHERE predicate for partial ones, and why ON CONSTRAINT rejects bare unique indexes.
Bring the operational edges: deferrable constraints rejected with 55000, unique-violation surprises during concurrent index rebuilds, and why inference survives an index swap.
Discuss how the choice of unique keys in the catalogue schema constrains every upsert written against it, and when an explicit constraint name is worth the coupling.
## What an arbiter is An **arbiter** is the unique index or constraint whose violation triggers the `ON CONFLICT` action instead of an error. A table can carry several unique indexes — in a product catalogue perhaps the identity primary key `product_id`, the supplier key `(supplier_id, supplier_sku)` and a unique `gtin` barcode — and the conflict target decides which of them the upsert reacts to. A collision on a unique index that is *not* an arbiter still raises `23505`. ## Unique index inference Writing `ON CONFLICT (supplier_id, supplier_sku)` does not name an index; it asks PostgreSQL to **infer** one. The rules from the `INSERT` reference page: 1. Every unique index on the table whose key contains **exactly** the listed columns or expressions, **without regard to order**, is chosen. An index on `(supplier_id, supplier_sku, region)` is not a match. 2. **Expression indexes** match only an expression target: an index on `(supplier_id, lower(supplier_sku))` needs `ON CONFLICT (supplier_id, lower(supplier_sku))`. 3. A **partial unique index** is inferred only when the statement supplies an `index_predicate` that satisfies its `WHERE`, e.g. `ON CONFLICT (supplier_sku) WHERE is_active`. A non-partial unique index on the same columns also satisfies that predicate and will be inferred too. 4. Optional `COLLATE` and opclass clauses narrow the match; they are rarely needed. 5. If no index qualifies, the statement fails with SQLSTATE `42P10`: 'there is no unique or exclusion constraint matching the ON CONFLICT specification'. Invalid indexes — for instance one left behind by a failed concurrent build — are skipped during inference. ## ON CONSTRAINT `ON CONFLICT ON CONSTRAINT catalog_product_supplier_sku_key` skips inference and names the arbiter. The name is resolved in **`pg_constraint`**, which has a consequence people trip over: - a `PRIMARY KEY` or `UNIQUE` constraint works; - an `EXCLUDE` constraint works with `DO NOTHING` but `DO UPDATE` fails with 'ON CONFLICT DO UPDATE not supported with exclusion constraints'; - a unique index created with `CREATE UNIQUE INDEX` is **not a constraint**, so naming it fails with `42704`, 'constraint ... for table ... does not exist'. Inference only ever considers unique indexes and skips exclusion constraints, including 18's `WITHOUT OVERLAPS` keys, which are exclusion constraints underneath. So `ON CONSTRAINT` is the natural choice when the arbiter is an exclusion constraint used with `DO NOTHING`, or when the team wants the arbiter spelled out by name. ## Comparison | | Column-list inference | `ON CONSTRAINT name` | |---|---|---| | Matches unique indexes without a constraint | yes | no (`42704`) | | Partial unique indexes | yes, with `WHERE` predicate | no, they cannot be constraints | | Exclusion constraint (incl. `WITHOUT OVERLAPS` keys) | never inferred | `DO NOTHING` only | | Survives replacing the index with an equivalent one | yes | only if the constraint name is kept | ## Rules that apply to every arbiter - Only **`NOT DEFERRABLE`** constraints and unique indexes can arbitrate. A deferrable one raises `55000`, 'ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters' — and with `DO NOTHING` and no target, every unique index is an arbiter, so one deferrable constraint on the table is enough to trigger it. - While `CREATE INDEX CONCURRENTLY` or `REINDEX CONCURRENTLY` runs on a unique index of the table, the manual warns that `ON CONFLICT` statements may unexpectedly fail with a unique violation; schedule rebuilds outside the sync window (building such an index without blocking writes is an index-maintenance subject). - On a partitioned catalogue, unique indexes must include the partition key, so the conflict target must as well. ## Choosing Prefer inference: the manual's own tip is that it keeps working when the index is replaced by an equivalent one built with `CREATE UNIQUE INDEX ... CONCURRENTLY` before the old one is dropped. A statement that names `ON CONSTRAINT catalog_product_supplier_sku_key` breaks the moment a migration recreates that key as a plain unique index or under another name, and the failure appears only at run time, in the next sync. Reach for `ON CONSTRAINT` when only a name can identify the arbiter, and keep the column list in step with the schema when the key changes — an added column in the unique key means every upsert's target must change too.
- Why does ON CONFLICT (supplier_sku) fail when the only unique index is partial, WHERE is_active?A partial unique index is inferred only when the statement repeats a predicate that implies the index's own, as in `ON CONFLICT (supplier_sku) WHERE is_active`. Without it inference finds no match and PostgreSQL raises `42P10`. Note the semantics: inactive rows are not covered by that index, so they never conflict at all.
- How does this compare with MySQL's INSERT ... ON DUPLICATE KEY UPDATE, which interviewers often contrast?MySQL's form names no target: by its public documentation it reacts to a duplicate on the primary key or any unique index, which is ambiguous when a table has several. PostgreSQL's `DO UPDATE` requires an explicit arbiter, and a collision on any other unique index still raises `23505`, so the behaviour is predictable on tables with multiple unique keys.
- ON CONFLICT (cols) is a hint; PostgreSQL falls back to any unique index if none match.
- ON CONSTRAINT can name any unique index, including one from CREATE UNIQUE INDEX.
- A unique index on (supplier_id, supplier_sku, region) is inferred for ON CONFLICT (supplier_id, supplier_sku).
- A DEFERRABLE unique constraint works as an arbiter once it is set IMMEDIATE.
- An exclusion constraint can drive ON CONFLICT DO UPDATE like a unique constraint.