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

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

质疑:

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: 没有唯一或排除 与 ON 冲突规范匹配的约束)

我还有一个独特的索引:

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)

在这种情况下，我确信这个帐户不存在。此外，当用户已经有一个帐户时，它不会输出错误。问题是，在某些情况下，如果还没有帐户，它也可以工作。查询完全相同。

101522 次浏览
小开

我没有机会玩 UPSERT，但我认为你有一个案件从 文件 :

请注意，这意味着非部分唯一索引(唯一索引) 将被推断(因此由 ON CONFLICT 使用) 如果这样一个索引满足所有其他条件是可用的 尝试推理失败，将引发错误。

小开

根据 那些文件,

所有 table _ name 唯一索引，无论顺序如何，都精确地包含 据此推断(选择)的冲突 _ target 指定的列/表达式作为仲裁者 如果指定了 index _ 谓词，则作为进一步的要求，必须指定 index _ 谓词 为了推理，满足仲裁者指标。

医生接着说,

使用[ index _ 谓词]来允许部分唯一索引的推断

以一种低调的方式，文档说，当使用部分索引和 在冲突，必须指定 index _ 谓词。它不是 推断出来的，我学到了这个 在这里 ，下面的示例演示了这一点。

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 _ 谓词，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;

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

小开

修复它的简单方法是将冲突列设置为 UNIQUE

小开

这个错误的简单解决方案

首先让我们通过一个简单的例子来看看 造成错误的原因。

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;

这将给出错误 No unique or exclusion constraint matching the ON CONFLICT，因为对 product_id和 category_id没有唯一的约束。可能有多个行具有相同的产品和类别 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
);

现在可以对这两列都使用 ON CONFLICT (product_id, category_id)，而不会出现任何错误。

简而言之: 无论在 on conflict中使用什么列，它们都应该具有唯一的约束。

小开

我为想要包含在 ON CONFLICT 子句中的所有列创建了一个 UNIQUE INDEX，而不是为每个列创建一个 UNIQUE INDEX，从而解决了同样的问题。

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

这将允许像下面这样查询

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;

提交答案