Re: create partitioned table with (like table INCLUDING ALL ) fails with "insufficient columns in UNIQUE constraint definition"

On 2018 Zvita 13, China 12:14:33 +03 Amit Langote wrote:
> On 2018/12/13 6:08, Stuart wrote:
> > Dear team,
> >
> > Another issue found with attempting to create partitioned table from (like
> > table including all). Primary key constraints don't get recognized if
> > there are other unique constraints and indexes in the original table.
> > Creating a non-partitioned table works ok using the same options.
> >
> >
> > =# \d+ knowledge
> >
> > Table
> > "public.knowl
> > edge"
> >
> > Column | Type | Collation | Nullable |
> > Default | Storage | Stats target | Description>
> > ---------------+-----------------------------+-----------+----------+-----
> > ------------------------------+----------+--------------+------------->
> > entry_date | timestamp(3) with time zone | | not null |
> > now() | plain | |
> > revision_date | timestamp(3) with time zone | | |
> > NULL::timestamp with time zone | plain | | entered_by
> > | text | | not null |
> > "current_user"() | extended | | revised_by
> > | text | | | ''::text
> > | extended | | source_id | bigint
> > | | |
> > | plain | | object_id | bigint
> > | | not null | nextval('resource_seq'::regclass) | plain
> > | | description | text |
> > | | ''::text | extended |
> > | category_id | bigint | | |
> > | plain | | producer_id
> > | bigint | | not null |
> > | plain | | released | date
> > | | | date(now())
> > | plain | | copyprotected | date
> > | | | | plain |
> > | isindexed | boolean | |
> > | false | plain | |
> > filepath | text | | |
> > ''::text | extended | | url
> > | text | | | ''::text
> > | extended | |>
> > Indexes:
> > "knowledge_pk" PRIMARY KEY, btree (object_id), tablespace "pgindex"
> > "knowledge_categoryfilepathurl_un" UNIQUE, btree (category_id,
> > filepath, url) WHERE filepath IS NULL AND url IS NULL, tablespace
> > "pgindex" "knowledge_filepath_un" UNIQUE CONSTRAINT, btree
> > (filepath), tablespace "pgindex" "knowledge_url_un" UNIQUE
> > CONSTRAINT, btree (url), tablespace "pgindex">
> > Inherits: products
> >
> >
> > =# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY
> > RANGE ( object_id ) ; ERROR: insufficient columns in UNIQUE constraint
> > definition
> > DETAIL: UNIQUE constraint on table "knowledge_new" lacks column
> > "object_id" which is part of the partition key.
> >
> > =# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY
> > RANGE ( object_id ) ; ERROR: insufficient columns in UNIQUE constraint
> > definition
> > DETAIL: UNIQUE constraint on table "knowledge_new" lacks column
> > "object_id" which is part of the partition key.
> >
> > =# alter table knowledge drop constraint knowledge_url_un ;
> > ALTER TABLE
> >
> > =# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY
> > RANGE ( object_id ) ; ERROR: insufficient columns in UNIQUE constraint
> > definition
> > DETAIL: UNIQUE constraint on table "knowledge_new" lacks column
> > "object_id" which is part of the partition key.
> >
> > =# alter table knowledge drop constraint knowledge_filepath_un ;
> > ALTER TABLE
> >
> > =# create table knowledge_new (like knowledge INCLUDING ALL ) PARTITION BY
> > RANGE ( object_id ) ; ERROR: insufficient columns in UNIQUE constraint
> > definition
> > DETAIL: UNIQUE constraint on table "knowledge_new" lacks column
> > "object_id" which is part of the partition key.
> I don't think this is a bug. It's rather a current *limitation* of
> defining UNIQUE constraints on partitioned tables that they cannot be
> created without including the partition key.
>
> Thanks,
> Amit

Amit,

Thanks for your response. So partitioning does not recognize a primary key
constraint on the partition key as a unique constraint? Shouldn't it?

Thanks,

Stuart