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.

Topheavy

maybe because it is partial index?.. – Phytophagous

Can you elaborate? – Topheavy

@VaoTsun partial indexes are supported by ON CONFLICT – Greeson

@Greeson Thank you. Isn't constraint Unique or PK obligatory for upsert? – Phytophagous

@TiagoBabo type within the index seems redundant (because of your predicate, it can only be 'PersonAccount') -- try with (person_id) WHERE type = 'PersonAccount' both in the index & in the ON CONFLICT specification – Greeson

@VaoTsun nah, if you look at the spec of conflict_target it clearly states that it can be based on unique indexes (even partial ones) -- Later also: conflict_target can perform unique index inference -- the error message is what somewhat misleading. – Greeson

@TiagoBabo do you have other unique indexes on accounts table? – Phytophagous

I wonder if you need . . . type::text = 'PersonAccount'::text. – Lupulin

@VaoTsun, yes, I have other unique indexes for that table. They are similar to the one I posted, but for other types and columns (generically speaking, for type = 'XAccount' and x_id). – Topheavy

@GordonLinoff if that was the case, wouldn't it always fail? – Topheavy

would you please give us sample of this random exception? commands and output of one successfull and one failed statements – Phytophagous

I edited the original post with a concrete example. – Topheavy

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.

Schluter

Answer 2

A 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.

Stercoraceous

Answer 3

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

Courageous

Answer 4

I did not have a chance to play with UPSERT, but I think you have a case from docs:

Note that this means a non-partial unique index (a unique index without a predicate) will be inferred (and thus used by ON CONFLICT) if such an index satisfying every other criteria is available. If an attempt at inference is unsuccessful, an error is raised.

Phytophagous

I saw that in the documentation as well, but from my interpretation, if it can't infer the index, it should always fail to do it, not just sometimes. Furthermore, that specifies the behaviour for non-partial unique indexes that are not created. In my case, I'm using a partial unique index. – Topheavy

Answer 5

I solved the same issue by creating one UNIQUE INDEX for ALL columns you want to include in the ON CONFLICT clause, not one UNIQUE INDEX for each of the columns.

CREATE TABLE table_name (
  element_id UUID NOT NULL DEFAULT gen_random_uuid(),
  timestamp TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
  col1 UUID NOT NULL,
  col2 STRING NOT NULL ,
  col3 STRING NOT NULL ,
  CONSTRAINT "primary" PRIMARY KEY (element_id ASC),
  UNIQUE (col1 asc, col2 asc, col3 asc)
);

Which will allow to query like

INSERT INTO table_name (timestamp, col1, col2, col3) VALUES ('timestamp', 'uuid', 'string', 'string')
ON CONFLICT (col1, col2, col3)
DO UPDATE timestamp = EXCLUDED.timestamp, col1 = EXCLUDED.col1, col2 = excluded.col2, col3 = col3.excluded;

Giusto

useful advice, but in UNIQUE() you can't pass ASC/DESC – Decant

Answer 6

If column already exists you can type the following command:

ALTER TABLE table_name
ADD CONSTRAINT unique_column_name UNIQUE (column_name);

Replacing with your table_name and column_name

Indication