我有以下实体属性值表:
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条目。
我正在此表上运行以下更新插入语句:
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子句使用。我想知道为什么在这种情况下不使用它。我在这里错过了什么,或者我犯了什么错误?
必须使用索引谓词才能使用部分唯一索引。阅读文档:
index_predicate
用于允许推理部分唯一索引。可以推断出任何满足谓词的索引(实际上不一定是部分索引)。遵循创建索引格式。
在这种情况下:
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;
另一个例子:
> 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]
active = false
的name = greg
和colour = blue
现在将更新为active = true
的行,其余部分将入