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.
