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@xxxxxxx> 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.
