PostgreSQL constraint to prevent overlapping ranges

Question

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

See here: postgresql.org/docs/current/…

Patrick
– Patrick

2022-10-17 17:30:51 +00:00
Commented Oct 17, 2022 at 17:30 — Patrick
– Patrick, Commented Oct 17, 2022 at 17:30

Laurenz Albe · Accepted Answer · 2022-10-18 02:09:07Z

9

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

edited Oct 18, 2022 at 2:09

answered Oct 17, 2022 at 17:32

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL constraint to prevent overlapping ranges

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related