No unique or exclusion constraint matching the ON CONFLICT

Question

I'm getting the following error when doing the following type of insert:

Query:

INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING * 

Error:

SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)

I also have an unique INDEX:

CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type, person_id) WHERE ((type)::text = 'PersonAccount'::text); 

The thing is that sometimes it works, but not every time. I randomly get that exception, which is really strange. It seems that it can't access that INDEX or it doesn't know it exists.

Any suggestion?

I'm using PostgreSQL 9.5.5.

Example while executing the code that tries to find or create an account:

INSERT INTO accounts (type, person_id, created_at, updated_at) VALUES ('PersonAccount', 69559, '2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *  SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification) 

In this case, I'm sure that the account does not exist. Furthermore, it never outputs the error when the person has already an account. The problem is that, in some cases, it also works if there is no account yet. The query is exactly the same.

Answer 1

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 and upserting with ON CONFLICT, the index_predicate must be specified. It is not inferred for you. I learned this here, 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.

Answer 2

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 (so there can never be a conflict on them).

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     -- unique(product_id, category_id) -- OR this if you already have a primary key ); 

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

In short: Whatever column(s) you use with on conflict, they should have unique constraint.