没有与冲突匹配的唯一或排除约束

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执行失败（原因：错误：没有与冲突规范匹配的唯一或排除约束）

我也有一个独特的索引：

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

问题是它有时有效，但不是每次都有效。我随机得到这个例外，这真的很奇怪。它似乎无法访问该索引，或者不知道它存在。

有什么建议吗？

我使用的是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

解决这个问题的简单方法是将冲突列设置为唯一

Answer 2

首先，让我们用一个简单的例子来看看错误的原因。下面是将产品映射到类别的表格。

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

如果我们使用此查询：

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;

这将导致错误没有唯一或排除约束与冲突匹配，因为产品标识和类别标识没有唯一约束。可能有多行具有相同的产品和类别id组合（因此它们之间永远不会有冲突）。

对product_id和category_id使用唯一约束如下：

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

现在，您可以对两列使用冲突（产品标识、类别标识），而不会出现任何错误。

简而言之：无论你在冲突上使用什么列，它们都应该有唯一的约束。

Answer 3

根据医生的说法，

所有表名唯一索引（不考虑顺序）都会被推断（选择）为仲裁器索引，这些索引恰好包含冲突目标指定的列/表达式。如果指定了索引_谓词，作为推理的进一步要求，它必须满足仲裁器索引。

医生接着说，

使用[index_]谓词可以推断部分唯一索引

以一种轻描淡写的方式，文档说当使用部分索引并使用ON CONFLICT升级时，必须指定index_predicate。这不是为你推断的。我在这里学到了这一点，下面的例子证明了这一点。

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)

如果没有索引_谓词，我们会得到一个错误：

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

但是如果你包含索引谓词，其中（（类型）：：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;

那么就没有错误，什么都不做是荣幸的。