pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (5+ messages)  latest in thread

Message-ID: <e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net>
Permalink:  ../e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net/
Also on:    postgresql.org/message-id/e50ba059-36ce-1450-a950-3ef887a554c7@gmx.net

 · 

reply

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