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
> 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.
