在PostgreSQL中执行upsert时，在ON CONFLICT子句中未使用部分索引。

Question

我有以下实体属性值表：

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个true is_active条目”的限制，并将索引更改为：

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

我在Postgres网站上阅读的文档中看到，在ON CONFLICT子句中将使用部分索引。我想知道为什么在这种情况下没有使用它。我可能错过了什么，或者犯了什么错误？

- Vaibhav

---

不相关，但是：您是否知道255不是某种神奇的值，并且与varchar列的例如200或345的限制相比没有任何优势。 - user330315

Answer 1

若要使用部分唯一索引，您必须使用索引谓词。请参阅文档：

index_predicate

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

在这种情况下：

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;

- klin

---

我错过了！谢谢！ - Vaibhav

Answer 2

另一个例子：

> users
id |  name   |  colour  |  active
---+---------+----------+--------
1  | 'greg'  |  'blue'  |  false
2  | 'kobus' |  'pink'  |  true

--------------------------------------------------------------------------
CREATE UNIQUE INDEX index_name
  --columns applicable to partial index
  ON users (name, colour)
  --partial index condition   **
  WHERE not active

--------------------------------------------------------------------------
INSERT INTO users (name, colour, active)
    --multiple inserts
VALUES ('greg', 'blue', false),  --this already exists for [false], conflict
       ('pieter', 'blue', true),  
       ('kobus', 'pink', false)  --this already exists for [true], no conflict
ON CONFLICT (name, colour)
    --partial index condition  **same as original partial index condition
WHERE not active
    --conflict action
  DO UPDATE SET
        active = not EXCLUDED.active
        --on conflict example, change some value, i.e. update [active]

将 name = greg 且 colour = blue 且 active = false 的行更新为 active = true，其余行将被插入。

- Kevin Potgieter