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

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
constraintsdon't get recognized if there are other unique constraints and indexes in the original table.  Creating a
non-partitionedtable works ok using the same options.
 
> 
> 
> =# \d+ knowledge
>                                                             Table "public.knowledge"
>     Column     |            Type             | Collation | Nullable |              Default              | Storage  |
Statstarget | 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