Re: Partitioning on the date part of a timestamp & PK issues

On Thu, Jul 15, 2021 at 1:10 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:

>
> primary key (guid, tid, seq)
> ) partition by range (extract(date from ts));
>
> The PK is the PK, so I'm not sure what options I have there.
>

You are indirectly hitting the documented limitation that:

"Unique constraints (and hence primary keys) on partitioned tables must
include all the partition key columns. This limitation exists because the
individual indexes making up the constraint can only directly enforce
uniqueness within their own partitions; therefore, the partition structure
itself must guarantee that there are not duplicates in different
partitions."

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW

So if you want to enforce PKs here you need to both add the physical column
to the table AND add the same to your primary key.

David J.