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

[Dionisis Kontominas]

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