exclude constraint with filtered row comparison

user_9426128 0
Viewed 106

The example is based on https://www.postgresql.org/docs/current/btree-gist.html

Let's say I have a schema:

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 prevents two different animals from being in the same cage. What I want is to have that constraint only if one of the animals is_aggressive IS TRUE. So there can be a deer with zebra in the cage, as long as none of the has is_aggressive flag set to TRUE.

How do I do this?

constraints postgresql sql
1 Answers

If you simply want to prevent that aggressive and non aggressive animals to live in the same cage, you might wanna add is_aggressive to the EXCLUDE constraint and cast it to a type that is supported by btree_gist, e.g. 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 <>)
);

This will work:

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);

And this will fail:

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

Note that it is still possible to insert two aggressive animals to the same cage! To prevent it you could add an UNIQUE INDEX to the table, so that on top of the EXCLUDE constraint it checks the incoming records to see if there is an aggressive animal already living in a specific cage:

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

Now this will also fail:

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.

If you want to perform more complex checks, I suggest you take a look at triggers.

Demo: db<>fiddle

As pointed out by @Arkaduisz Noster in the comments, if it is allowed for at least 1 aggressive animal to share a cage with non aggressive ones, one can add a WHERE clause to the CONSTRAINT:

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

user_22753880
Related