postgresql unique index preventing overlaping

My table permission looks like:

id serial,
person_id integer,
permission_id integer,
valid_from date,
valid_to date

I'd like to prevent creating permissions which overlaps valid_from, valid_to date

eg.

1 | 1 | 1 | 2010-10-01 | 2999-12-31
2 | 1 | 2 | 2010-10-01 | 2020-12-31
3 | 2 | 1 | 2015-10-01 | 2999-12-31

this can be added:

4 | 1 | 3 | 2011-10-01 | 2999-12-31 - because no such permission
5 | 2 | 1 | 2011-10-10 | 2999-12-31 - because no such person
6 | 1 | 2 | 2021-01-01 | 2999-12-31 - because doesn't overlaps id:2

but this can't

7 | 1 | 1 | 2009-10-01 | 2010-02-01 - because overlaps id:1
8 | 1 | 2 | 2019-01-01 | 2022-12-31 - because overlaps id:2
9 | 2 | 1 | 2010-01-01 | 2016-12-31 - beacuse overlaps id:3

I can do outside checking but wonder if possible to do it on database

Solution

A unique constraint is based on an equality operator and cannot be used in this case, but you can use an exclude constraint. The constraint uses btree operators <> and =, hence you have to install btree_gist extension.

create extension if not exists btree_gist;

create table permission(
    id serial,
    person_id integer,
    permission_id integer,
    valid_from date,
    valid_to date,
    exclude using gist (
        person_id with =, 
        permission_id with =, 
        daterange(valid_from, valid_to) with &&)
);

These inserts are successful:

insert into permission values
    (1, 1, 1, '2010-10-01', '2999-12-31'),
    (2, 1, 2, '2010-10-01', '2020-12-31'),
    (3, 2, 1, '2015-10-01', '2999-12-31'),
    (4, 1, 3, '2011-10-01', '2999-12-31'),
    (5, 3, 1, '2011-10-10', '2999-12-31'), -- you meant person_id = 3 I suppose
    (6, 1, 2, '2021-01-01', '2999-12-31'),
    (7, 1, 1, '2009-10-01', '2010-02-01'); -- ranges do not overlap!

but this one is not:

insert into permission values
    (8, 1, 2, '2019-01-01', '2022-12-31');

ERROR:  conflicting key value violates exclusion constraint "permission_person_id_permission_id_daterange_excl"
DETAIL:  Key (person_id, permission_id, daterange(valid_from, valid_to))=(1, 2, [2019-01-01,2022-12-31)) conflicts with existing key (person_id, permission_id, daterange(valid_from, valid_to))=(1, 2, [2010-10-01,2020-12-31)).

Try it in db<>fiddle.