From: | Siddharth Jain <siddhsql(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Question on creating keys on partitioned tables |
Date: | 2023-03-31 15:53:41 |
Message-ID: | CAPqV3pTuTV6qPpvWT8f67yd8EPP79vk2Ww5ddJkwmEdWCCQdXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Laurenz.
I think the two are equivalent. If not, could you please explain why?
On Fri, Mar 31, 2023 at 6:46 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:
> On Thu, 2023-03-30 at 17:05 -0700, Siddharth Jain wrote:
> > I have this question. Say I create a partitioned table on column X.
> >
> > Option 1:
> >
> > I add a primary key on (X,Y). Y is another column. Even though Y is a
> globally unique PK (global meaning it is unique across partitions, not just
> in one partition), Postgres does not allow me to
> > create a PK on Y in a partitioned table.
> >
> > Option 2:
> >
> > I add PK on Y on each of the partitions
> >
> > Are these not equivalent? If not, which is better and why?
>
> No, they are not equivalent.
>
> Option 2 comes closer to guaranteeing uniqueness for column X, so use that.
>
> > PS: This is what my best friend had to say:
> >
> > [...] If you are using the "table inheritance" approach [...]
>
> Don't even consider that. Declarative partitioning is so much better.
>
> Yours,
> Laurenz Albe
>
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Ramsey | 2023-03-31 15:57:25 | Re: Cannot terminate backend |
Previous Message | Arnaud Lesauvage | 2023-03-31 14:41:54 | RE: Cannot terminate backend |