Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 <>)
);

CREATE TABLE

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 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

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

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

TRUNCATE TABLE

CREATE INDEX

-- all non aggressive animals, so it will work.
INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'zebra',false);
INSERT INTO zoo VALUES(1,'deer',false);

-- cage 2 is empty, so it will work.
INSERT INTO zoo VALUES(2,'lion',true);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

 -- fail: cage 1 already has animals
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).

-- fail: there is already 1 aggressive animal in cage 2
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.