Re: Postgres 11 Insufficient columns in PRIMARY KEY constraint definition

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