From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
        Nagaraj Raj <nagaraj.sf@yahoo.com>,
        Pg Bugs <pgsql-bugs@postgresql.org>
Subject: Re: ERROR: insufficient columns in the PRIMARY KEY constraint
 definition
In-reply-to: 
 <CAApHDvrGCKVEQeJ0Tchxkq86NXvF=HzVoROv_jGAGYv61P8Tuw@mail.gmail.com>
References: <64062533.78364.1601415362244.ref@mail.yahoo.com>
 <64062533.78364.1601415362244@mail.yahoo.com>
 <CAApHDvrGCKVEQeJ0Tchxkq86NXvF=HzVoROv_jGAGYv61P8Tuw@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Wed, 30 Sep 2020 11:05:42 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <724328.1601418149.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 29 Sep 2020 18:22:29 -0400
Message-ID: <724329.1601418149@sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> 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 prima=
ry 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 pa=
rtitioning columns

or perhaps

ERROR: unique constraint on partitioned table must include all partitionin=
g columns

The DETAIL seems fine as-is:

DETAIL:  PRIMARY KEY constraint on table "l_billing_account_p" lacks colum=
n "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