On 06.07.2020 13:45, Anastasia Lubennikova wrote:
The previous discussion of automatic partition creation [1] has addressed static and dynamic creation of partitions and ended up with several syntax proposals.
In this thread, I want to continue this work.
...
[1] https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre
Syntax proposal v2, that takes into account received feedback.
I compared the syntax of other databases. You can find an overview here [1]. It
seems that there is no industry standard, so every DBMS has its own
implementation. I decided to rely on a Greenplum syntax, as the most similar to
the original PostgreSQL syntax.
New proposal is:
CREATE TABLE numbers(int number)
PARTITION BY partition_method (list_of_columns)
USING (partition_desc)
where partition_desc is:
MODULUS n
| VALUES IN (value_list), [DEFAULT PARTITION part_name]
| START ([datatype] 'start_value') END ([datatype] 'end_value') EVERY (partition_step), [DEFAULT PARTITION part_name]
where partition_step is:
[datatype] [number | INTERVAL] 'interval_value'
example:
CREATE TABLE years(int year)
PARTITION BY RANGE (year)
USING
(START (2006) END (2016) EVERY (1),
DEFAULT PARTITION other_years);
It is less wordy than the previous version. It uses a free keyword option
style. It covers static partitioning for all methods, default partition for
list and range methods, and can be extended to implement dynamic partitioning
for range partitions.
[1] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS
[2] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company