Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table:
CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT NULL, max INTEGER NOT NULL ); INSERT INTO heights_in_feet (size, min, max) VALUES ('wee', 0, 2), ('not so wee', 3, 6), ('friggin huge', 7, 10);
To prevent ambiguity, I add an exclusion constraint:
ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap EXCLUDE USING gist (int4range("min", "max") WITH &&);
At first, this seems to be correct:
INSERT INTO heights_in_feet (size, min, max) VALUES ('medium', 4, 5); -- INSERT fails, as it should
However, it doesn't work with empty ranges:
INSERT INTO heights_in_feet (size, min, max) VALUES ('medium', 5, 5); -- INSERT succeeds!
This is bad: it's now ambiguous whether 5 feet is "medium" or "not so wee".
The issue is that Postgres considers
int4range(5,5) to be empty, and thus non-overlapping:
SELECT int4range(3, 6) && int4range(4, 5); -- t SELECT int4range(3, 6) && int4range(5, 5); -- f
It does consider
(3,6) to contain
SELECT int4range(3, 6) @> int4range(5, 5);
... but 1) I can't use that in an exclusion constraint because it's not commutative and 2) it also returns true for
int4range(3, 6) @> int4range(50, 50); apparently any empty range is "contained by" any non-empty range.
How can I exclude new rows that would create ambiguous classifications?