Search Postgresql Archives

Re: Question on creating keys on partitioned tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux