There is no single limitation or limitation of exception corresponding to ON CONFLICT

Question

I get the following error while performing 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 * 

Mistake:

SQL execution failed (Cause: ERROR: There is no unique exception constraint that conforms to the ON CONFLICT specification)

I also have a unique INDEX:

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

The fact is that sometimes it works, but not every time. I randomly get this exception, which is really strange. It seems that he cannot access this INDEX or he does not know that it exists.

Any suggestion?

I am using PostgreSQL 9.5.5.

Example when executing 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 am sure that the account does not exist. In addition, it never displays an error if a person already has an account. The problem is that in some cases it also works if the account does not already exist. The request is exactly the same.

Answer 1

I didn’t have a chance to play with UPSERT, but I think you have a case from docs :

Note that this means an odd unique index (a unique index without a predicate) will be output (and therefore ON CONFLICT is used) if such an index satisfies any other criteria. If the withdrawal attempt was unsuccessful, an error occurred.

Answer 2

According to the docs ,

All unique table_name indexes, which, regardless of order, contain exactly the columns / expressions specified in the context_target parameter, are displayed (selected) as arbitration indexes. If index_predicate is specified, it, as an additional requirement for output, must satisfy the arbiter indices.

Documents keep saying

[index_predicate] used to enable partial unique indexes

In an underestimated form, the documents say that when using a partial index and adding with ON CONFLICT, you must specify index_predicate . This is not displayed for you. I found out about it 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 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 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 mistake, and NOTHING is observed.