Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

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 <shammat(at)gmx(dot)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.
>
>
>