How can I exclude range overlaps if one range may be empty?

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 `(5,5)`:

``````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?

Use inclusive ranges

Adding an argument like `'[]'` to `int4range` tells it to use inclusive ranges, meaning that, eg, `(5,5)` is not empty because it contains `5`. Eg:

``````SELECT int4range(3, 6) @> int4range(5, 5, '[]');   -- t
SELECT int4range(3, 6) @> int4range(50, 50, '[]'); -- f
``````

Therefore, this exclusion works as I expected:

``````ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
EXCLUDE USING gist (int4range("min", "max", '[]') WITH &&);
``````