PostgreSQL constraint to prevent overlapping ranges-CodePudding

I wonder if it's possible to write a constraint that would make ranges unique. These ranges are represented as two string-typed columns bottom and top. Say, If I have the following row in a database,

| id | bottom |  top  |
|----|--------|-------|
| 1  | 10000  | 10999 |

inserting the row (2, 10100, 10200) would immediately result in constraint violation error.
P.S I can't switch to integers, unfortunately -- only strings

CodePudding user response：

Never store numbers as strings, and always use a range data type like int4range to store ranges. With ranges, you can easily use an exclusion constraint:

ALTER TABLE tab ADD EXCLUDE USING gist (bottom_top WITH &&);

Here, bottom_top is a range data type.