skip to content

In PostgreSQL's INSERT ... ON CONFLICT, how is the arbiter index chosen, and when would you use ON CONSTRAINT instead of a column list?

level: middleimportance: must knowfreq 58%

answer

  1. inference, not a hint
  2. exactly the listed columns
  3. partial index needs its predicate
  4. only names in pg_constraint

basics

~20 s

PostgreSQL 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 s

With 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 lines
sql
CREATE 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

for a junior

Know that the column list must match a real unique index or constraint, otherwise the upsert errors instead of falling back.

for a middle

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.

for a senior

Bring the operational edges: deferrable constraints rejected with 55000, unique-violation surprises during concurrent index rebuilds, and why inference survives an index swap.

for a principal

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.