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?