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.a...@cybertec.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 >
