>> 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
I think that your position about primary keys in partitional tables is not right.
If we see regular table, one-field primary key is cross-table unique.
In partitional tables for users view we MUST also seen unique one-field primary key because this is user requirement
andanother keys can destroy logic between regular and partitional tables and functionality of partitioning becomes
useless.
For administrators of table we not-MAY, but MUST see one-field unique primary key in cross-table realisation.
All another realizations are plugging holes in a hurry and non-logical at global review of engine.