Proposal for syntax to support creation of partition tables when creating parent table

Hi Hackers,

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