在Postgresql中执行upsert时，在ON CONFLICT子句中未使用部分索引 [英] Partial Index not used in ON CONFLICT clause while performing an upsert in Postgresql

问题描述

我有以下实体属性值表：

I have the following Entity Attribute value table :

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语句：

I am running the following upsert statement on this table :

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 。

What I am wondering is why it is not using the unique partial index key_value_pair_key_if_is_active_true_unique.

如果我在任何时间点放开，对于任何给定的密钥，只有1个真正的is_active条目应该存在 >子句并将索引更改为：

The upsert works if I let go of the "at any point in time, for any given key, only 1 true is_active entry should be present" clause and change the index to:

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

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

I read documentation on the Postgres website that partial indexes will be used by the ON CONFLICT clause. I wonder why it is not used in this case. What am I missing here, or what error am I making?

推荐答案

你必须使用索引谓词来使用部分唯一指数。请阅读文档：

You have to use an index predicate to use a partial unique index. Read in the documentation:

index_predicate

index_predicate

用于允许推断部分唯一索引。可以推断出满足谓词的任何索引（实际上不需要是部分索引）。遵循CREATE INDEX格式。

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, value, is_active) VALUES ('temperature','20', false) 
ON CONFLICT (key) WHERE is_active
DO UPDATE
SET value = '33', is_active = true;

这篇关于在Postgresql中执行upsert时，在ON CONFLICT子句中未使用部分索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！