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

Question

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?

Answer 1

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