Horton

Re: ERROR: insufficient columns in the PRIMARY KEY constraint definition

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Date: 2020-09-29T22:05:42Z
Lists: pgsql-bugs 
On Wed, 30 Sep 2020 at 10:36, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> Is it mandatory/necessary that the `partition column` should be a primary key? cause if I  include `load_dttm` as `PK` then its working fine.

Yes, this is required.  There is no concept of an index over all
partitions in PostgreSQL.  The requirement of having the partition key
a subset of the primary key allows us to implement primary keys by
just having individual unique indexes on each partition.  The fact
that it does not work is not a bug.

There's mention in [1] section 5.10.2.3. "Unique constraints on
partitioned tables must include all the partition key columns. This
limitation exists because PostgreSQL can only enforce uniqueness in
each partition individually.". That text likely should also mention
PRIMARY KEY constraints. That probably should be changed

David

[1] https://www.postgresql.org/docs/11/ddl-partitioning.html

Commits

  1. Reword partitioning error message

  2. Doc: Improve clarity on partitioned table limitations