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

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Wed, 30 Sep 2020 at 10:36, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)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.

Indeed. However, this complaint shows that the error message is not clear
enough. I propose changing it to

ERROR: unique constraint on partitioned table must be a superset of the partitioning columns

or perhaps

ERROR: unique constraint on partitioned table must include all partitioning columns

The DETAIL seems fine as-is:

DETAIL: PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.

> 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

Meh. If you've read that bit you probably already understand that
pkeys are unique constraints. I think the problem is with the error
text not the docs.

regards, tom lane