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., stackoverflow.com/a/13000715/8887313. Not sure that this is the most efficient way or even whether it will work... — ATOMP
– ATOMP, Commented Jul 13, 2020 at 16:41
Please see this solution: dba.stackexchange.com/a/110583/211987 — Mike Organek
– Mike Organek, Commented Jul 13, 2020 at 16:45
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
– Raqib, Commented Jul 13, 2020 at 17:53

Raqib · Accepted Answer · 2020-07-13 17:56:28Z

4

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

answered Jul 13, 2020 at 17:56

Raqib

1,44213 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Postgres: unique constraint on integer range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related