MIME-Version: 1.0
References: <CAJCZko+pA8o3HDBuGzxzYjMo=Gm_8yBLk7Sod3Fdo-i0WAwLuA@mail.gmail.com>
 <CAKFQuwarcToEtVZHOo5O_CBe4ZD2mGZM35R28kLn=MNi0D6X2w@mail.gmail.com>
In-Reply-To: <CAKFQuwarcToEtVZHOo5O_CBe4ZD2mGZM35R28kLn=MNi0D6X2w@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 28 Sep 2024 00:49:35 -0400
Message-ID: <CANzqJaAknAV3vhxQ3tnJkGbHEmQJwWhi5jVW7wYkwZx4WQh2DA@mail.gmail.com>
Subject: Re: Regarding use of single column as primary key on partitioned table
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000729a11062326b3e3"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAknAV3vhxQ3tnJkGbHEmQJwWhi5jVW7wYkwZx4WQh2DA%40mail.gmail.com>
Precedence: bulk

--000000000000729a11062326b3e3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Sep 28, 2024 at 12:39=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Friday, September 27, 2024, Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>>
>> ERROR:  unique constraint on partitioned table must include all
>> partitioning columns
>> DETAIL:  PRIMARY KEY constraint on table "bet" lacks column "placedon"
>> which is part of the partition key.
>> test=3D>
>>
>> Can't we use primary key on singal column(betid) on partitioned table
>> rather than using composite key (placedon,betid)?
>>
>
> No.  It would be misleading to allow such a thing because a unique index
> can only span a single partition.
>

That is IMO a serious (and probably unfixable, given how PG stores tables)
flaw in PG's partitioning design.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Sep 28, 2024 at 12:39=E2=80=AFAM =
David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g=
.johnston@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">On Friday, September 27, 2024=
, Durgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9@gmail.com" target=
=3D"_blank">maheshpostgres9@gmail.com</a>&gt; wrote:<br><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br>ERROR: =C2=A0uni=
que constraint on partitioned table must include all partitioning columns<b=
r>DETAIL: =C2=A0PRIMARY KEY constraint on table &quot;bet&quot; lacks colum=
n &quot;placedon&quot; which is part of the partition key.<br>test=3D&gt;<b=
r></div><div><br></div><div>Can&#39;t we use primary key on singal column(b=
etid) on partitioned table rather than using composite key (placedon,betid)=
?</div></div></blockquote><div><br></div><div>No.=C2=A0 It would be mislead=
ing to allow such a thing because a unique index can only span a single par=
tition.</div><div></div></blockquote></div><div><br></div><div>That is IMO =
a serious (and probably unfixable, given how PG stores tables) flaw in PG&#=
39;s partitioning design.</div><div><br></div><span class=3D"gmail_signatur=
e_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; crustacean!</div></div=
></div></div></div></div>

--000000000000729a11062326b3e3--