Re: Postgres 11 Insufficient columns in PRIMARY KEY constraint definition

Thanks alot Alvaro.
For my use case, should i fall back to INHERITANCE and trigger based
partitioning (
https://zaiste.net/table_inheritance_and_partitioning_with_postgresql/)
?
Changing my primary key to include the date column will not work for the
logic required. I also would not want to eliminate the unique key
constraints.
What do u advise me to do as i anticipate that the tables are going to grow
so large on quarterly basis ?

I could also use ROLL UP table but that would work if i could apply
triggers on views

On Wed, Dec 19, 2018 at 7:49 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2018-Dec-19, Joshua Muzaaya wrote:
>
> > DETAIL: PRIMARY KEY constraint on table lacks column "sdate" which is
> part
> > of the partition key. SQL state: 0A000
> >
> > I have a table which i am trying to create with RANGE partitioning using
> > the timestamp column. But my primary doesnot need to have this timestamp
> > column, its another column.
>
> Yeah, that won't work.
>
> > Why is postgres 11 asking me to add this
> > partition key in the primary key ?
>
> Implementation restrictions. We may lift it in future releases, but
> don't hold your breath.
>
> > The documentation lacks this or am missing something ?
>
> It seems the docs are unclear on this ... failed edits. The PRIMARY KEY
> part of it is clear; they say:
>
> PRIMARY KEY constraints share the restrictions that UNIQUE constraints
> have when placed on partitioned tables.
>
> But under UNIQUE you find this:
>
> When establishing a unique constraint for a multi-level partition
> hierarchy, all the columns in the partition key of the target
> partitioned table, as well as those of all its descendant partitioned
> tables, must be included in the constraint definition.
>
> in reality it doesn't matter than the hierarchy is multi-level or not --
> the restriction applies to all partitioned setups.
>
> I think this may be clearer:
>
> When establishing a unique constraint on a partitioned table, all the
> columns in the partition key of the partitioned table must be
> included in the constraint definition. In case of a multi-level
> partition hierarchy, this applies to the set of all columns used in
> partition keys across the whole hierarchy.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>