Help Center/ GaussDB/ Centralized_8.x/ Partitioned Table/ Partitioned Table O&M Management/ ADD PARTITION/ Adding a Partition to a Level-2 Partitioned Table
Updated on 2024-06-07 GMT+08:00
View PDF

Adding a Partition to a Level-2 Partitioned Table

You can run ALTER TABLE ADD PARTITION to add a range or list partition to a level-2 partitioned table. If a level-2 partition definition is declared under the new partition, the database creates the corresponding level-2 partition based on the definition. If no level-2 partition definition is declared under the new partition, the database automatically creates a default level-2 partition.

For example, add a partition to the level-2 partitioned table range_list_sales and create four level-2 partitions. 
ALTER TABLE range_list_sales ADD PARTITION date_202005 VALUES LESS THAN ('2020-06-01') TABLESPACE tb1
(
    SUBPARTITION date_202005_channel1 VALUES ('0', '1', '2'),
    SUBPARTITION date_202005_channel2 VALUES ('3', '4', '5') TABLESPACE tb2,
    SUBPARTITION date_202005_channel3 VALUES ('6', '7'),
    SUBPARTITION date_202005_channel4 VALUES ('8', '9')
);
Alternatively, add only a partition to the level-2 partitioned table range_list_sales. 
ALTER TABLE range_list_sales ADD PARTITION date_202005 VALUES LESS THAN ('2020-06-01') TABLESPACE tb1;
The preceding statement is equivalent to the following SQL statement: 
ALTER TABLE range_list_sales ADD PARTITION date_202005 VALUES LESS THAN ('2020-06-01') TABLESPACE tb1
(
    SUBPARTITION date_202005_channel1 VALUES (DEFAULT)
);

If the level-1 partitioning policy of a level-2 partitioned table is HASH, the partition cannot be added by running ALTER TABLE ADD PARTITION.

Parent topic: ADD PARTITION