没有与 ON CONFLICT 匹配的唯一或排除约束 [英] No unique or exclusion constraint matching the ON CONFLICT

问题描述

在执行以下类型的插入时出现以下错误:

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

查询:

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 *

错误:

SQL 执行失败(原因:ERROR: there is no unique or exclude约束匹配 ON CONFLICT 规范)

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);

问题是有时它有效，但不是每次都有效.我随机得到那个异常，真是奇怪.好像不能访问INDEX 或者它不知道它存在.

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.

有什么建议吗?

我使用的是 PostgreSQL 9.5.5.

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.

推荐答案

Per 文档、

所有 table_name 唯一索引，不考虑顺序，完全包含冲突目标指定的列/表达式被推断(选择)为仲裁者索引.如果指定了 index_predicate，作为进一步的要求，它必须对于推理，满足仲裁索引.

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.

文档继续说，

[index_predicate 被用于允许推断部分唯一索引

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

以一种低调的方式，文档说当使用部分索引和使用 ON CONFLICT 进行更新插入，必须指定 index_predicate.它不是为你推断.我学会了这个这里，下面的示例演示了这一点.

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);

所以我们有:

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

如果没有 index_predicate，我们会得到一个错误:

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

但是如果你包含 index_predicate，WHERE ((type)::text = 'PersonAccount'::text):

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.

这篇关于没有与 ON CONFLICT 匹配的唯一或排除约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！