Re: Question on creating keys on partitioned tables

On Fri, Mar 31, 2023 at 9:07 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Siddharth Jain <siddhsql(at)gmail(dot)com> writes:
> > I think the two are equivalent. If not, could you please explain why?
>
> Well, they're formally equivalent if you require there to be only one
> X value per partition (ie, PARTITION BY LIST with only one listed value
> per partition); if there's more, they're not the same thing.
>

Yes. This is what I have. I think I understand now. Is following correct?

when a PK is created on (X,Y) on the parent table what happens internally
is that the command is run individually on each of the child tables.
nothing more. nothing less.

>
> Neither one guarantees that Y is globally unique. We have no mechanism
> for enforcing uniqueness across partitions except for partition key
> columns.
>
> regards, tom lane
>