MIME-Version: 1.0
References: 
 <AM9P251MB033090199F1A3C2B1742A50A98499@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
 <CAJexoS+F37aQStLD4chj51EhtLyUWFNTtC4Vduq4XqR_bFwYCQ@mail.gmail.com>
 <AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
 <3585170.1663310489@sss.pgh.pa.us>
 <AM9P251MB03309E0FC9FFEC4552F81ED298489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
 <CAApHDvoVCXU8_S46VBX6j+ff6n-dHLb6jNnfUF094db9ccBjKg@mail.gmail.com>
In-Reply-To: 
 <CAApHDvoVCXU8_S46VBX6j+ff6n-dHLb6jNnfUF094db9ccBjKg@mail.gmail.com>
From: Geri Wright <geri.w123@gmail.com>
Date: Thu, 22 Sep 2022 06:39:27 -0400
Message-ID: 
 <CAKSgRY7WQtA2wyFkX-rE6oLMLm=bfr9EphQy2_H6x0Affz528A@mail.gmail.com>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
To: David Rowley <dgrowleyml@gmail.com>
Cc: Inzamam Shafiq <inzamam.shafiq@hotmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Steve Midgley <science@misuse.org>, pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000038eaad05e941aef2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKSgRY7WQtA2wyFkX-rE6oLMLm%3Dbfr9EphQy2_H6x0Affz528A%40mail.gmail.com>
Precedence: bulk

--00000000000038eaad05e941aef2
Content-Type: text/plain; charset="UTF-8"

You can create a Constraint trigger to check the new values of the desired
columns when updating or insering them. This trigger will fire for each
partition but checks the data in the entire table.  The trigger should
obtain an advisory lock for the unique columns . Here is a blog for more
information.
https://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

On Tue, Sep 20, 2022, 5:46 AM David Rowley <dgrowleyml@gmail.com> wrote:

> On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq
> <inzamam.shafiq@hotmail.com> wrote:
> > Is there anyway we can create constraint on column only which are not
> part of partition key?
>
> Unfortunately, there is no way to create a unique or primary key
> constraint unless the constraint contains all columns from the
> partition key.   This is explained in the limitations section in [1].
>
> If you need such a constraint, then you might want to consider
> changing your partition key.
>
> David
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>
>
>

--00000000000038eaad05e941aef2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">You can create a Constraint trigger to check the new valu=
es of the desired columns when updating or insering them. This trigger will=
 fire for each partition but checks the data in the entire table.=C2=A0 The=
 trigger should obtain an advisory lock for the unique columns . Here is a =
blog for more information.=C2=A0<div dir=3D"auto"><a href=3D"https://blog.i=
oguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html">=
https://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints=
-part-1.html</a><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"=
ltr" class=3D"gmail_attr">On Tue, Sep 20, 2022, 5:46 AM David Rowley &lt;<a=
 href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border=
-left:1px #ccc solid;padding-left:1ex">On Fri, 16 Sept 2022 at 19:52, Inzam=
am Shafiq<br>
&lt;<a href=3D"mailto:inzamam.shafiq@hotmail.com" target=3D"_blank" rel=3D"=
noreferrer">inzamam.shafiq@hotmail.com</a>&gt; wrote:<br>
&gt; Is there anyway we can create constraint on column only which are not =
part of partition key?<br>
<br>
Unfortunately, there is no way to create a unique or primary key<br>
constraint unless the constraint contains all columns from the<br>
partition key.=C2=A0 =C2=A0This is explained in the limitations section in =
[1].<br>
<br>
If you need such a constraint, then you might want to consider<br>
changing your partition key.<br>
<br>
David<br>
<br>
[1] <a href=3D"https://www.postgresql.org/docs/12/ddl-partitioning.html" re=
l=3D"noreferrer noreferrer" target=3D"_blank">https://www.postgresql.org/do=
cs/12/ddl-partitioning.html</a><br>
<br>
<br>
</blockquote></div>

--00000000000038eaad05e941aef2--