Reputation: 13
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
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