From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?
Date: Wed, 26 Jul 2023 11:18:21 +0200
Message-ID: <e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net> (raw)
In-Reply-To: <CAB4Evu1_fyZ_oqxr+0afdSBZ6kg3_Jv0Us6CyG=ix86354QZOA@mail.gmail.com>
References: <CAB4Evu1_fyZ_oqxr+0afdSBZ6kg3_Jv0Us6CyG=ix86354QZOA@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: shammat@gmx.net
Subject: Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?
In-Reply-To: <e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net>
References: <CAB4Evu1_fyZ_oqxr+0afdSBZ6kg3_Jv0Us6CyG=ix86354QZOA@mail.gmail.com> <e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox