通过筛选行比较排除约束

Question

示例基于https://www.postgresql.org/docs/current/btree-gist.html

假设我有一个模式:

CREATE TABLE zoo (
cage   INTEGER,
animal TEXT,
is_agressive BOOLEAN,
constraint no_different_animals_in_same_cage EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

no_different_animals_in_same_cage防止两个不同的动物在同一个笼子里。我想要的是只有当其中一只动物is_aggressive IS TRUE。所以在cage中可以有一只鹿和斑马，只要没有一个is_aggressive的标志设置为TRUE。

我该怎么做?

Answer 1

如果你只是想防止攻击性动物和非攻击性动物生活在同一个笼子里，你可能需要将is_aggressive添加到EXCLUDE约束中，并将其强制转换为btree_gist支持的类型，例如int4(1 = true, 0 = false):

CREATE TABLE zoo (
cage   INTEGER,
animal TEXT,
is_aggressive BOOLEAN,
CONSTRAINT no_different_animals_in_same_cage 
EXCLUDE USING gist (cage WITH =, animal WITH <>, int4(is_aggressive) WITH <>)
);

INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'deer',false);
INSERT INTO zoo VALUES(2,'lion',true);
INSERT INTO zoo VALUES(2,'lion',true);

这将失败:

INSERT INTO zoo VALUES(1,'lion',true);
ERROR:  conflicting key value violates exclusion constraint "no_different_animals_in_same_cage"
DETAIL:  Key (cage, animal, int4(is_aggressive))=(1, lion, 1) conflicts with existing key (cage, animal, int4(is_aggressive))=(1, zebra, 0).
SQL state: 23P01

请注意，仍然有可能将两只具有攻击性的动物放入同一个笼子!为了防止这种情况，你可以在表中添加一个UNIQUE INDEX约束，这样在EXCLUDE约束的基础上，它会检查传入的记录，看看是否有攻击性动物已经生活在特定的笼子里:

CREATE UNIQUE INDEX aggressive_animals_live_alone
ON zoo (cage,is_aggressive) WHERE (is_aggressive);

现在也会失败:

INSERT INTO zoo VALUES(2,'lion',true);
INSERT INTO zoo VALUES(2,'killer bunny',true);
ERROR:  duplicate key value violates unique constraint "aggressive_animals_live_alone"
DETAIL:  Key (cage, is_aggressive)=(2, t) already exists.

如果你想执行更复杂的检查，我建议你看一下触发器。

Demo:db<>fiddle

正如@Arkaduisz Noster在评论中指出的那样，如果允许至少一只攻击性动物与非攻击性动物共用一个笼子，则可以在CONSTRAINT中添加WHERE条款:

CREATE TABLE zoo (
cage   INTEGER,
animal TEXT,
is_aggressive BOOLEAN,
CONSTRAINT no_different_animals_in_same_cage 
EXCLUDE USING gist (cage WITH =, animal WITH <>) WHERE (is_aggressive)
);

Demo:db<>fiddle