Automatic Partitioning of Level-1 Partitioned Tables

To enable the automatic list partitioning function, you need to specify the AUTOMATIC keyword when creating a level-1 list partitioned table. Automatic partitioning of level-1 list partitioned tables supports multi-column partition keys.

For example, create a list partitioned table that supports automatic partitioning.

gaussdb=# CREATE TABLE auto_list (c1 int, c2 int) 
PARTITION BY LIST (c1) AUTOMATIC
(
    PARTITION p1 VALUES (1, 2, 3),
    PARTITION p2 VALUES (4, 5, 6)
);

If the inserted data cannot match any existing partition, a partition is automatically created. The range of the new partition is defined as a single key.

-- Insert data 9 into the partition key. The sys_p1 partition is automatically created and defined as VALUES (9).
gaussdb=# INSERT INTO auto_list VALUES (9, 0);

This function is equivalent to the following commands:

ALTER TABLE auto_list ADD PARTITION sys_p1 VALUES (9);
INSERT INTO auto_list VALUES (9, 0);
gaussdb=# DROP TABLE auto_list;