postgresql - 条件唯一约束未正确更新

Question

我需要在一个列上强制执行唯一性，但仅当其他列为真时。例如:

create temporary table test(id serial primary key, property character varying(50), value boolean);
insert into test(property, value) values ('a', false);
insert into test(property, value) values ('a', true);
insert into test(property, value) values ('a', false);

我用条件索引强制唯一性:

create unique index on test(property) where value = true;

到目前为止一切顺利，当我尝试更改值设置为 true 的行时出现问题。如果我这样做，它会起作用:

update test set value = new_value from (select id, id=3 as new_value from test where property = 'a')new_test where test.id = new_test.id

但当我这样做时却没有:

update test set value = new_value from (select id, id=1 as new_value from test where property = 'a')new_test where test.id = new_test.id

然后我得到:

ERROR:  duplicate key value violates unique constraint "test_property_idx"
DETAIL:  Key (property)=(a) already exists.

********** Error **********

ERROR: duplicate key value violates unique constraint "test_property_idx"
SQL state: 23505
Detail: Key (property)=(a) already exists.

基本上，如果值为 true 的行的主键值大于当前行(为真)，则它会起作用。关于如何规避它的任何想法？

当然可以:

update test set value = false where property='a';
update test set value = true where property = 'a' and id = 1;

但是，我从节点运行这些查询，最好只运行一个查询。

我正在使用 Postgres 9.5

Answer 1

你的问题是UPDATE statements cannot have an ORDER BY clause in SQL (它可以在一些 RDBMS 中有，但在 PostgreSQL 中没有)。

通常的解决方案是使约束可延迟。但是您使用部分唯一索引，并且索引不能声明为可延迟的。

使用 exclusion constraint相反:它们是唯一约束的概括并且也可以是部分的。

ALTER TABLE test
  ADD EXCLUDE (property WITH =) WHERE (value = true)
  DEFERRABLE INITIALLY DEFERRED;