执行以下类型的插入时,我收到以下错误：
查询：
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)
在这种情况下,我确信该帐户不存在.此外,当此人已经拥有帐户时,它永远不会输出错误.问题是,在某些情况下,如果还没有帐户,它也可以使用.查询完全相同.
解决方法
我没有机会玩UPSERT,但我认为你有一个案例
docs：
docs：
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.
