CODEWITHSUNDEEP
sqlpostgresql
Raqib
Raqib

Reputation: 1442

Postgres: unique constraint on integer range

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.

Upvotes: 2

Views: 684

Answers (1)

Raqib
Raqib

Reputation: 1442

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

Upvotes: 4

Related Questions