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?
Also, should I post this question on pgsql-sql as more appropriate?
Thank you In Advance!
Regards,
Dionisis