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

On Wed, 30 Sep 2020 at 11:22, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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

I prefer the former. Although I'd rather see the constraint type
mentioned explicitly rather than using the word "unique" regardless of
what the constraint type is.

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

I think you're assuming too much. If you don't think too hard about
it, it might seem reasonable that we can implement something for a
primary key constraint, because there can only be at most 1 per table,
but not a unique constraint there can be any number.

David