Hi Thomas, Thank you very much for your reply and comment.
I am trying to avoid writing trigger code to handle this requirement. I will do so and try your suggestion. I believe also that the partial constraint you propose to me should be in the end: ... WHERE (NOT f_is_deleted) as I do not want the deleted records to participate in the constraint logic. Kindest regards, Dionisis On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <sham...@gmx.net> wrote: > Dionisis Kontominas schrieb am 26.07.2023 um 11:00: > > Hello all, > > > > In the Subject I mention what I am intending to do. Letme put some > context; this is my table: > > > > portal_user_role > > ( > > f_id INTEGER NOT NULL, > > f_portal_user_id INTEGER NOT NULL, > > f_portal_role_id INTEGER NOT NULL, > > f_is_active BOOLEAN NOT NULL, > > f_is_deleted BOOLEAN NOT NULL, > > f_start_date DATE NOT NULL, > > f_end_date DATE, > > f_created_on TIMESTAMP WITH TIME ZONE NOT NULL, > > f_updated_on TIMESTAMP WITH TIME ZONE, > > f_created_by CHARACTER VARYING(255) NOT NULL, > > f_updated_by CHARACTER VARYING(255), > > CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id), > > CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user_id) > REFERENCES portal_user (f_id), > > CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role_id) > REFERENCES portal_role (f_id), > > EXCLUDE USING gist (f_portal_user_id WITH =, > > f_portal_role_id WITH =, > > DATERANGE(f_start_date, COALESCE(f_end_date, 'infinity'), '[]') WITH > &&) > > ); > > > > So, this table has a range of dates [f_start_date, f_end_date] that I > > do not want two records to overlap, for the same user, the same role > > and also when the f_is_deleted is TRUE only. > > > > I do not care for the records when the f_is_deleted is FALSE on them; > i.e. they should not be part of the restriction/constraint. > > > > How can I achieve this? > > You can add a WHERE clause to the exclusion constraint (the condition must > be enclosed in parentheses though): > > EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, > DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted) > > Note that you don't need COALESCE(f_end_date, 'infinity') because a > daterange will treat null as infinity anyways. > > >