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

查询：

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)

我也有一个独特的INDEX：

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

问题是有时候它有效,但不是每次都有效.我随机得到
那个例外,这真的很奇怪.它似乎无法访问它
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)

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

解决方法

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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!