No unique or exclusion constraint matching the ON CONFLICT

Per the docs,

All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes. If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes.

The docs go on to say,

[index_predicate are u]sed to allow inference of partial unique indexes

In an understated way, the docs are saying that when using a partial index andupserting with ON CONFLICT, the index_predicate must be specified. It is notinferred for you. I learned thishere, and the following example demonstrates this.

CREATE TABLE test.accounts (    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,    type text,    person_id int);CREATE UNIQUE INDEX accounts_note_idx on accounts (type, person_id) WHERE ((type)::text = 'PersonAccount'::text);INSERT INTO  test.accounts (type, person_id) VALUES ('PersonAccount', 10);

so that we have:

unutbu=# select * from test.accounts;+----+---------------+-----------+| id |     type      | person_id |+----+---------------+-----------+|  1 | PersonAccount |        10 |+----+---------------+-----------+(1 row)

Without index_predicate we get an error:

INSERT INTO  test.accounts (type, person_id) VALUES ('PersonAccount', 10) ON CONFLICT (type, person_id) DO NOTHING;-- ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

But if instead you include the index_predicate, WHERE ((type)::text = 'PersonAccount'::text):

INSERT INTO  test.accounts (type, person_id) VALUES ('PersonAccount', 10)ON CONFLICT (type, person_id)WHERE ((type)::text = 'PersonAccount'::text) DO NOTHING;

then there is no error and DO NOTHING is honored.

A possible simple solution of this error

First of all let's see the cause of error with a simple example. Here is the table mapping products to categories.

create table if not exists product_categories (    product_id uuid references products(product_id) not null,    category_id uuid references categories(category_id) not null,    whitelist boolean default false);

If we use this query:

INSERT INTO product_categories (product_id, category_id, whitelist)VALUES ('123...', '456...', TRUE)ON CONFLICT (product_id, category_id)DO UPDATE SET whitelist=EXCLUDED.whitelist;

This will give you error No unique or exclusion constraint matching the ON CONFLICT because there is no unique constraint on product_id and category_id. There could be multiple rows having the same combination of product and category id.

Solution:

Use unique constraint on both product_id and category_id like this:

create table if not exists product_categories (    product_id uuid references products(product_id) not null,    category_id uuid references categories(category_id) not null,    whitelist boolean default false,    primary key(product_id, category_id) -- This will solve the problem);

Now you can use ON CONFLICT (product_id, category_id) for both columns without any error.

The easy way to fix it is by setting the conflicting column as UNIQUE