Given two integers, start and end, and a foreign key, how do I define a unique constraint on the interval start:end inclusive, and the foreign_key
Given I have the following entries in my table:
+-------+-----+--------------------------------------+
| start | end | foreign_key |
+-------+-----+--------------------------------------+
| 10 | 20 | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+-------+-----+--------------------------------------+
| 40 | 60 | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+-------+-----+--------------------------------------+
Then, the following inserts should fail:
+-------+-----+--------------------------------------+
| start | end | foreign_key |
+-------+-----+--------------------------------------+
| 30 | 50 | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+-------+-----+--------------------------------------+
| 12 | 18 | 04ef8258-917c-46d6-8db3-9c704d3f4fbd |
+-------+-----+--------------------------------------+
What I have tried thus far:
alter table some_table
add constraint unique_interval_to_foreign_key_constraint
unique (start, end, foreign_key)
This does not work as it only defines the unique constraint on discrete points and the foreign key, and not the range.
Any help would be appreciated.
Adding answer for completeness:
CREATE EXTENSION btree_gist;
ALTER TABLE some_table
ADD CONSTRAINT unique_interval_to_foreign_key_constraint
EXCLUDE USING gist
(foreign_key WITH =,
int4range(start, end, '[]') WITH &&
);