Postgres: unique constraint on integer range

Question

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.

you might be able to use a `CHECK` constraint with a subquery checking that no records exist overlapping the range, i.e., https://stackoverflow.com/a/13000715/8887313. Not sure that this is the most efficient way or even whether it will work... — ATOMP, Jul 13 '20 at 16:41
Please see this solution: https://dba.stackexchange.com/a/110583/211987 — Mike Organek, Jul 13 '20 at 16:45
You want an [exclusion constraint](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION) — , Jul 13 '20 at 16:46
Additional question: How would I query such a table with the exclusion constraint i.e. how do I handle the ON CONFLICT UPDATE RETURNINg id case? — Raqib, Jul 13 '20 at 17:53

score 3 · Answer 1 · answered Jul 13 '20 at 17:56

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

Postgres: unique constraint on integer range

1 Answers1

Linked