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