From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Scalia <jayknowsunix@gmail.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Creating a partition table
In-reply-to: <D8D76052-3A69-4E31-8E5C-EB965FE6D53A@gmail.com>
References: <98825.1604077633@sss.pgh.pa.us>
 <D8D76052-3A69-4E31-8E5C-EB965FE6D53A@gmail.com>
Comments: In-reply-to John Scalia <jayknowsunix@gmail.com>
	message dated "Fri, 30 Oct 2020 13:48:53 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <101519.1604081342.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 30 Oct 2020 14:09:02 -0400
Message-ID: <101520.1604081342@sss.pgh.pa.us>
Precedence: bulk

John Scalia <jayknowsunix@gmail.com> writes:
> Ok I tried what Laurent had suggested and now the error is:
> Insufficient columns in PRIMARY KEY constraint definition.

> Now, the attribute the devs want is not a key value in the original tabl=
e, but if it were that wouldn=E2=80=99t make a great deal of sense for a p=
artition. So, now why did it gripe?

FWIW, we recently rephrased that message as "unique constraint on
partitioned table must include all partitioning columns", because
people were finding it confusing.

The reason for the restriction is that a partitioned index is really
just a collection of separate indexes on the individual leaf partitions
(just as the partitioned table is a collection of plain tables).
So it can't enforce uniqueness across partitions unless such uniqueness
is guaranteed by the partitioning scheme.

Yeah, this is an implementation restriction, but I don't foresee it
ever going away.  If the indexes weren't independent then you'd lose
the scalability benefits that partitioning is meant to provide.

			regards, tom lane