在Postgresql中执行upsert时,在ON CONFLICT子句中未使用部分索引

发布时间：2020-12-13 18:07:02 所属栏目：百科来源：网络整理

导读：我有以下实体属性值表： CREATE TABLE key_value_pair ( id serial NOT NULL PRIMARY KEY,key varchar(255) NOT NULL,value varchar(255),is_active boolean);CREATE UNIQUE INDEX key_value_pair_key_if_is_active_true_unique ON key_value_pair (key) WHE

我有以下实体属性值表：

CREATE TABLE key_value_pair (
    id serial NOT NULL PRIMARY KEY,key varchar(255) NOT NULL,value varchar(255),is_active boolean
);

CREATE UNIQUE INDEX key_value_pair_key_if_is_active_true_unique ON key_value_pair (key) WHERE is_active = true;

此表中的示例条目是：

id |     key     | value | is_active 
----+-------------+-------+-----------
  1 | temperature | 2     | f
  2 | temperature | 12    | f
  3 | temperature | 15    | f
  4 | temperature | 19    | f
  5 | temperature | 23    | t
(5 rows)

因此,在任何时间点,对于任何给定的密钥,只应存在1个真正的is_active条目.

我在此表上运行以下upsert语句：

INSERT INTO key_value_pair (key,value,is_active) VALUES ('temperature','20',true) 
ON CONFLICT (key,is_active)
DO UPDATE
SET value = '33',is_active = true;

但是,它失败了：

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

我想知道的是它没有使用唯一的部分索引key_value_pair_key_if_is_active_true_unique.

如果我放开“在任何时间点,只应存在1个真正的is_active条目”子句并将索引更改为：

CREATE UNIQUE INDEX key_value_pair_key_if_is_active_true_unique ON key_value_pair (key,is_active);

我阅读了Postgres网站上的文档,其中部分索引将由ON CONFLICT子句使用.我想知道为什么在这种情况下不使用它.我在这里错过了什么,或者我犯了什么错误？

您必须使用索引谓词来使用部分唯一索引.阅读 the documentation:

index_predicate

Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format.

在这种情况下：

INSERT INTO key_value_pair (key,false) 
ON CONFLICT (key) WHERE is_active
DO UPDATE
SET value = '33',is_active = true;

（编辑：李大同）

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