Richard Helper
Richard Helper

Reputation: 13

PostgreSQL constraint to prevent overlapping ranges

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

Upvotes: 1

Views: 297

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

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.

If you have to stick with the broken data model using two string columns, you can strip # characters and still have an exclusion constraint with

ALTER TABLE tab ADD EXCLUDE USING gist (
   int4range(
      CAST(trim(bottom, '#') AS integer),
      CAST(trim(top,    '#') AS integer),
      '[]'
   ) WITH &&
);

Upvotes: 8

Related Questions