Re: Table partition with primary key in 11.3

User <ya(at)penek(dot)org> writes:
> CREATE TABLE public.test1 (
> x1 integer NOT NULL,
> x2 integer NOT NULL,
> CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
> ) PARTITION BY RANGE (x2);

> This query works in 11.1 but fails in 11.3 with messages:

> ERROR: insufficient columns in PRIMARY KEY constraint definition
> DETAIL: PRIMARY KEY constraint on table "test1" lacks column "x2" which
> is part of the partition key.
> SQL state: 0A000

Indeed, that primary key is no good. It was a bug that 11.1
allowed it, which was fixed here:

Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Branch: master [0ad41cf53] 2019-01-14 19:28:10 -0300
Branch: REL_11_STABLE Release: REL_11_2 [74aa7e046] 2019-01-14 19:25:19 -0300

Fix unique INCLUDE indexes on partitioned tables

We were considering the INCLUDE columns as part of the key, allowing
unicity-violating rows to be inserted in different partitions.

Concurrent development conflict in eb7ed3f30634 and 8224de4f42cc.

Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/20190109065109.GA4285@telsasoft.com

regards, tom lane