From b25c343b4df6aef79d1e804f5c2468a345cb9596 Mon Sep 17 00:00:00 2001 From: Koval Dmitry Date: Tue, 28 Mar 2023 10:50:19 +0300 Subject: [PATCH v22 3/4] Documentation for ALTER TABLE SPLIT PARTITION/MERGE PARTITIONS commands --- doc/src/sgml/ref/alter_table.sgml | 124 +++++++++++++++++++++++++++++- 1 file changed, 121 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 9670671107..9fb31df250 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -37,6 +37,13 @@ ALTER TABLE [ IF EXISTS ] name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE [ IF EXISTS ] name DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ] +ALTER TABLE [ IF EXISTS ] name + SPLIT PARTITION partition_name INTO + (PARTITION partition_name1 { FOR VALUES partition_bound_spec | DEFAULT }, + PARTITION partition_name2 { FOR VALUES partition_bound_spec | DEFAULT } [, ...]) +ALTER TABLE [ IF EXISTS ] name + MERGE PARTITIONS (partition_name1, partition_name2 [, ...]) + INTO partition_name where action is one of: @@ -1106,14 +1113,99 @@ WITH ( MODULUS numeric_literal, REM + + SPLIT PARTITION partition_name INTO (PARTITION partition_name1 { FOR VALUES partition_bound_spec | DEFAULT }, PARTITION partition_name2 { FOR VALUES partition_bound_spec | DEFAULT } [, ...]) + + + + This form split a single partition of the target table. Hash-partitioning + is not supported. Bounds of new partitions should not overlap with new and + existing partitions (except partition_name). + If the split partition is DEFAULT partition, one of new partitions must be DEFAULT. + In case one of new partitions or one of existing partitions is DEFAULT, + new partitions partition_name1, + partition_name2, ... can have spaces + between partitions bounds. If the partitioned table does not have a DEFAULT + partition, the DEFAULT partition can be defined as one of the new partitions. + + + In case new partitions do not contains DEFAULT partition and the partitioned table + does not have a DEFAULT partition, the following must be true: sum bounds of + new partitions partition_name1, + partition_name2, ... should be + equal to bound of split partition partition_name. + One of the new partitions partition_name1, + partition_name2, ... can have + the same name as split partition partition_name + (this is suitable in case of splitting a DEFAULT partition: we split it, but after + splitting we have a partition with the same name). + Only simple, non-partitioned partition can be split. + + + + + + MERGE PARTITIONS (partition_name1, partition_name2 [, ...]) INTO partition_name + + + + This form merge several partitions into one partition of the target table. + Hash-partitioning is not supported. If DEFAULT partition is not in the + list of partitions partition_name1, + partition_name2 [, ...]: + + + + For range-partitioned tables is necessary that the ranges + of the partitions partition_name1, + partition_name2 [, ...] can + be merged into one range without spaces and overlaps (otherwise an error + will be generated). The combined range will be the range for the partition + partition_name. + + + + + For list-partitioned tables the values lists of all partitions + partition_name1, + partition_name2 [, ...] are + combined and form a list of values of partition + partition_name. + + + + If DEFAULT partition is in the list of partitions partition_name1, + partition_name2 [, ...]: + + + + The partition partition_name + will be the DEFAULT partition. + + + + + For range- and list-partitioned tables the ranges and lists of values + of the merged partitions can be any. + + + + The new partition partition_name + can have the same name as one of the merged partitions. Only simple, + non-partitioned partitions can be merged. + + + + All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, - ATTACH PARTITION, and - DETACH PARTITION can be combined into + ATTACH PARTITION, DETACH PARTITION, + SPLIT PARTITION, and MERGE PARTITIONS + can be combined into a list of multiple alterations to be applied together. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large @@ -1356,7 +1448,8 @@ WITH ( MODULUS numeric_literal, REM partition_name - The name of the table to attach as a new partition or to detach from this table. + The name of the table to attach as a new partition or to detach from this table, + or the name of split partition, or the name of the new merged partition. @@ -1772,6 +1865,31 @@ ALTER TABLE measurement DETACH PARTITION measurement_y2015m12; + + To split a single partition of the range-partitioned table: + +ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2023 INTO + (PARTITION sales_feb2023 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'), + PARTITION sales_mar2023 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'), + PARTITION sales_apr2023 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01')); + + + + To split a single partition of the list-partitioned table: + +ALTER TABLE sales_list SPLIT PARTITION sales_all INTO + (PARTITION sales_west FOR VALUES IN ('Voronezh', 'Smolensk', 'Bryansk'), + PARTITION sales_east FOR VALUES IN ('Magadan', 'Khabarovsk', 'Vladivostok'), + PARTITION sales_central FOR VALUES IN ('Moscow', 'Kazan', 'Volgograd')); + + + + To merge several partitions into one partition of the target table: + +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) + INTO sales_all; + + -- 2.39.2