CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Tiago Babo
Tiago Babo

Reputation: 1344

No unique or exclusion constraint matching the ON CONFLICT

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.

Upvotes: 113

Views: 226532

Answers (7)

dain
dain

Reputation: 6689

I wasted hours on this, you'll get the same error if your SQL expects a UNIQUE INDEX to exist with the same set of columns, but it isn't there for some reason (I think in my case it was messed up migrations in my local dev db).

Upvotes: 0

unutbu
unutbu

Reputation: 879361

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.

Upvotes: 67

Antonin GAVREL
Antonin GAVREL

Reputation: 11219

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

Upvotes: 1

Ruben1
Ruben1

Reputation: 91

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;

Upvotes: 3

Ali Sajjad Rizavi
Ali Sajjad Rizavi

Reputation: 4480

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.

Upvotes: 56

idirall22
idirall22

Reputation: 364

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

Upvotes: 14

Vao Tsun
Vao Tsun

Reputation: 51446

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.

Upvotes: 2

Related Questions