3

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?

  • Oh! I think the issue may be that I have exclusive ranges, not inclusive ones. Tinkering... – Nathan Long Jan 5 '16 at 11:58
  • 1
    It's an int range, right? So you could use the (5,6) range instead, the only one that contains 5 and only 5. – ypercubeᵀᴹ Jan 5 '16 at 12:02
  • 1
    I was assuming inclusive-exclusive ranges in previous comment. – ypercubeᵀᴹ Jan 5 '16 at 12:04
  • @YperSillyCubeᵀᴹ oh, I see - yes, that makes sense. Thanks! – Nathan Long Jan 5 '16 at 12:09
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 &&);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.