sql - 没有匹配 ON CONFLICT 的唯一或排除约束

Question

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

查询:

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 execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)

我还有一个唯一的索引:

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

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

有什么建议吗？

我正在使用 PostgreSQL 9.5.5。

执行尝试查找或创建帐户的代码时的示例:

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)

在这种情况下，我确定该帐户不存在。此外，当此人已有帐户时，它永远不会输出错误。问题是，在某些情况下，如果还没有帐户，它也可以使用。查询完全相同。

Answer 1

根据 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.

文档继续说，

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

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

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，我们会得到一个错误:

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

INSERT INTO  test.accounts (type, person_id) VALUES ('PersonAccount', 10)
ON CONFLICT (type, person_id)
WHERE ((type)::text = 'PersonAccount'::text) DO NOTHING;

然后就没有错误了，什么都不做。