how to write exclude constraint with grouped rows postgresql?

52 Views Asked by At

i have some_table (postresql):

create table some_table (
    id int primary key,
    s int not null,
    d int not null,
    idx_grp int,
    start_date date not null,
    end_date date nit null
)

First tasks was to create unique pair (s, d) on date interval (start_date, end_date). I can solve this problem via constraint

EXCLUDE USING gist (s with =, d with =, daterange(start_date, end_date, '[]') WITH &&),

But now i need use column idx_grp. Idea is that column is subgroup of pair (s, d). If exist pairs (s_x, d_x, g_1) and (s_x, d_x, g_2) on the same date interval its correct. If g=0 of some pair (s_x, d_x) this means pair (s_x, d_x) belongs all groups. Therefore there cannot be a situation when exist any other row with pair (s_x, d_x) in the table. Additionally if exist pair (s_x, d_x) with some group (g_x) then you cannot add pair with all groups (s_x, d_x, 0). How can i update or rewrite my constraint to solve this situation?

0

There are 0 best solutions below