Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qOaf6-008L8n-I6 for pgsql-general@arkaria.postgresql.org; Wed, 26 Jul 2023 09:18:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qOaf3-003PLJ-W7 for pgsql-general@arkaria.postgresql.org; Wed, 26 Jul 2023 09:18:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qOaf3-003PLA-Kf for pgsql-general@lists.postgresql.org; Wed, 26 Jul 2023 09:18:25 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qOaf1-000SKA-68 for pgsql-general@lists.postgresql.org; Wed, 26 Jul 2023 09:18:25 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1690363101; x=1690967901; i=shammat@gmx.net; bh=4/pagAwoNCGV6nF9qd7mqS5b16m2ndoKAwfCDAACYxQ=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=IA0XviW3tkjGZmDOA593kaWH+qPCgnwLfN/qfP77BRTHrrbItvoS4VIgHpMKtM9N6LmGz5r Q05CJ310CSq9ri4htYEUWvtM/e2r7H5aydmhiYhfZXB8bubt4PgBWtxEDJphi4GTzQOt6yQw7 XPfggB7URIm9MTIWSgA2RZaljXQTTYdLKEEEYSLEMg8Croz9HIo6KL/y6UenmVBC5i/FTwwJk NBzAiQuQyVt9ugJU/rHhfTQXo2SczqBHb4YhWlCqzl78muzIT9TLwSGQhfy7iJoMX7fFVgGFn hztZIxHEBTgQ7sXDsdhmZNIm43h413ct+gI2AK6taF7Ra4BIplWQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.8.0.19] ([185.40.248.10]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1Mt79F-1pa4TE2qGf-00tX4N for ; Wed, 26 Jul 2023 11:18:21 +0200 Subject: Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? To: pgsql-general@lists.postgresql.org References: From: Thomas Kellerer Message-ID: Date: Wed, 26 Jul 2023 11:18:21 +0200 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:xsNWv0mYXvGd3G4JMyUVXV59bO6zPY+yrWsPzRIXRWmZkYKvSMz WlE9bWC62DqVhPPzTBbWtzppTADlQap7Tex6F6st+LCIKyMDasLek7E2lk1o9I7HTUp2a7G jifuBH58T88sPGxvaKAJlzx5o3iAH2M1WYsYtdw7OjXfV4toB/JGSAOLJKz1SlWs+e2R30I aXW24pSQzLDXYiqiT9zvQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:MeN3LQ7C7Xk=;01U8WRkwERzD7wLeQTJRnsQ8OYW i3AjyMctNBPT/zlXdvopUgeuzatU7FsUSVX6AUJdkXJIYd0hDvJFpdwtBcxA1TyLjccnTfCpK mJKbaFDclih5AmnidgBxSDKDWK5hYectVEKPXVWyCYn01erGijNdhUdvjhdXvJY6lLmxVFPP4 yJBO4dW6misRUn9LljZ9okrxO+PBM5phBQ5rLqt19xh2SfF4y5Grne924pB0PfxXbw0u5EBi4 fBrNe9P78OUvj6AqwCIfJCUG+SHtUUAT95KohN7DBh16nAFzMrhUmSnoaDmg12DB16YlZxxAO cdkQNdnkWr4L/9hkBzUzGLWQ2JqPR8p8sLiyZ5NzNnfsgt9zF+EvQMhBWg1OIndW4qldci+Wl 3/sj009IkDVB+p+R2SeKAIHbjc8NOiCxeGShj6VKJGcsCBnz4kSAgoZSg+U1oLMVlftUL8Wez 1x8KtxcZyl0OfpWTjipY0yQ1T14JYlqN3n/7GyemqhLtpZKqRmHv1Xn85nCqr8HlfksEZga3O 5/lsMZVZGN9NdKXgI3sJOkb06RINJYQki7TyMOJnxjj1bw78+vQj8s/WUwQuoVCnjj4XeojW7 2W5I8iCdHLDwxUpLkNKYU6iGZNnEb+XZ1dpJGRHUPjD1Xn8IGZa4q3pfE5ns5E42ygbVft81O ckA6og9/wg6mdEyPFbdhOAevsHv/xhvxMDYNMybOSxk0IPAhFAPWq8jwoLhdcqf0R6Va5I2ke rsbDAGqQCHze03OQ/gK9hVA+IP07PeUBRa8Fb0JsdUqybTbjz+YxJRXyKTl/8QRHr6VkUdvEp YoYlIFjn2HZ2zGH0AvnHX1geJSjwlrqCYK3t8MYCSDKUgtqvxMItcr6j7ak8OQuDsGbAwA0nl 4k0LVp93IBOvtGouzxL5lyi5DeWkAKSZ1SiOYskdzDxBBbqb27u1ED5eIM40kGBXL/vIJSzsC AzwRiw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 conte= xt; this is my table: > > portal_user_role > ( > =C2=A0 =C2=A0 f_id INTEGER NOT NULL, > =C2=A0 =C2=A0 f_portal_user_id INTEGER NOT NULL, > =C2=A0 =C2=A0 f_portal_role_id INTEGER NOT NULL, > =C2=A0 =C2=A0 f_is_active BOOLEAN NOT NULL, > =C2=A0 =C2=A0 f_is_deleted BOOLEAN NOT NULL, > =C2=A0 =C2=A0 f_start_date DATE NOT NULL, > =C2=A0 =C2=A0 f_end_date DATE, > =C2=A0 =C2=A0 f_created_on TIMESTAMP WITH TIME ZONE NOT NULL, > =C2=A0 =C2=A0 f_updated_on TIMESTAMP WITH TIME ZONE, > =C2=A0 =C2=A0 f_created_by CHARACTER VARYING(255) NOT NULL, > =C2=A0 =C2=A0 f_updated_by CHARACTER VARYING(255), > =C2=A0 =C2=A0 CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id), > =C2=A0 =C2=A0 CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user= _id) REFERENCES portal_user (f_id), > =C2=A0 =C2=A0 CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role= _id) REFERENCES portal_role (f_id), > =C2=A0 =C2=A0 EXCLUDE USING gist (f_portal_user_id WITH =3D, > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 f_portal_role_id WITH =3D, > =C2=A0 =C2=A0 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.=C2=A0 > > 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 =3D, f_portal_role_id WITH = =3D, DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_delete= d) Note that you don't need COALESCE(f_end_date, 'infinity') because a datera= nge will treat null as infinity anyways.