Re: Proposal for syntax to support creation of partition tables whencreating parent table

Hello Muhammad,

I think that it may be better to have a partition spec which describes not 
the list of partitions, but what is wanted, letting postgres to do some 
more work.

See this thread:

https://www.postgresql.org/message-id/alpine.DEB.2.21.1907150711080.22273@lancre

> I want to propose an extension to CREATE TABLE syntax to allow the creation
> of partition tables along with its parent table using a single statement.
>
> In this proposal, I am proposing to specify the list of partitioned tables
> after the PARTITION BY clause.
>
> CREATE TABLE table_name (..)
>   PARTITION BY { RANGE | LIST | HASH } (..)
> (
>   list of partitions
> )  ;

> Below are a few examples of the proposed syntax, in a nutshell, I am
> leveraging the syntax currently supported by Postgres for creating
> partitioned tables. The purpose of this proposal is to combine the creation
> of the parent partition table and its partitions in one SQL statement.
>
> CREATE TABLE Sales (salesman_id INT, salesman_name TEXT, sales_region TEXT,
> hiring_date DATE, sales_amount INT )
>    PARTITION BY RANGE (hiring_date)
>    (
>    PARTITION part_one FOR VALUES FROM ('2008-02-01') TO ('2008-03-01'),
>    PARTITION part_two FOR VALUES FROM ('2009-02-01') TO ('2009-03-01'),
>    PARTITION part_def DEFAULT
>    );
>
> CREATE TABLE Sales2 (salesman_id INT, salesman_name TEXT, sales_region
> TEXT, hiring_date DATE, sales_amount INT )
>    PARTITION BY HASH (salesman_id)
>    (
>    PARTITION par_one FOR VALUES WITH (MODULUS 2, REMAINDER 0),
>    PARTITION par_two FOR VALUES WITH (MODULUS 2, REMAINDER 1)
>    );
>
> CREATE TABLE Sales3(salesman_id INT, salesman_name TEXT, sales_region TEXT,
> hiring_date DATE, sales_amount INT)
>    PARTITION BY LIST (sales_region)
>    (
>     PARTITION pt_one FOR VALUES IN ('JAPAN','CHINA'),
>     PARTITION pt_two FOR VALUES IN ('USA','CANADA'),
>     PARTITION pt_def DEFAULT
>    );
>
> -- Similarly for specifying subpartitions of partitioned tables
>
> CREATE TABLE All_Sales ( year INT, month INT, day INT, info TEXT)
>    PARTITION BY RANGE(year)(
>            PARTITION sale_2019_2020 FOR VALUES FROM (2019) TO (2021)
>            PARTITION BY LIST(month)
>            (
>             PARTITION sale_2019_2020_1 FOR VALUES IN (1,2,3,4)
>             PARTITION BY RANGE(day)(
>                 PARTITION sale_2019_2020_1_1 FOR VALUES FROM (1) TO (10)
>                 PARTITION BY HASH(info)
>                 (
>                  PARTITION sale_2019_2020_1_1_1 FOR VALUES WITH (MODULUS
> 2,REMAINDER 0),
>                  PARTITION sale_2019_2020_1_1_2 FOR VALUES WITH (MODULUS
> 2,REMAINDER 1)
>                 ),
>                 PARTITION sale_2019_2020_1_2 FOR VALUES FROM (10) TO (20),
>                 PARTITION sale_2019_2020_1_3 FOR VALUES FROM (20) TO (32)),
>             PARTITION sale_2019_2020_2 FOR VALUES IN (5,6,7,8),
>             PARTITION sale_2019_2020_3 FOR VALUES IN (9,10,11,12)
>            ),
>            PARTITION sale_2021_2022 FOR VALUES FROM (2021) TO (2023),
>            PARTITION sale_2023_2024 FOR VALUES FROM (2023) TO (2025),
>            PARTITION sale_default default
>            );
>
> This new syntax requires minimal changes in the code. I along with my
> colleague Movead.li have drafted a rough POC patch attached to this email.
>
> Please note that the patch is just to showcase the new syntax and get a
> consensus on the overall design and approach.
>
> As far as I know, there are already few ongoing discussions related to the
> partition syntax enhancements, but the proposed syntax will not interfere
> with these ongoing proposals. Here is a link to one such discussion:
> https://www.postgresql.org/message-id/alpine.DEB.2.21.1907150711080.22273%40lancre
>
> Please feel free to share your thoughts.
>
> Best Regards
>
> ...
> Muhammad Usama
> Highgo Software Canada
> URL : http://www.highgo.ca
> ADDR: 10318 WHALLEY BLVD, Surrey, BC
>

-- 
Fabien Coelho - CRI, MINES ParisTech