Using and Managing Partitioned Tables

Partitioned tables support most functions related to non-partitioned tables. For details, see the syntax related to ordinary tables in Developer Guide.

In addition, partitioned tables support many partition-level operation commands, including partition-level DQL/DML operations (such as SELECT, INSERT, UPDATE, DELETE, UPSERT and MERGE INTO), partition-level DDL operations (such as ADD, DROP, TRUNCATE, EXCHANGE, SPLIT, MERGE, MOVE and RENAME), partition-level VACUUM/ANALYZE, and various partitioned indexes. For details about how to use related commands, see DQL/DML Operations on a Partitioned Table, Partitioned Indexes, Partitioned Table O&M Management, and the chapter corresponding to each syntax and command in Developer Guide.

A partition-level operation command is generally performed by specifying a partition name or a partition value. For example, the syntax of a command may be as follows:

sql_action [ t_name ] { PARTITION | SUBPARTITION } { p_name | (p_name) };
sql_action [ t_name ] { PARTITION | SUBPARTITION } FOR (p_value);

You can specify the partition name p_name or partition value p_value to perform operations on a specific partition. In this case, services apply only to the target partition and do not affect other partitions. If you specify p_name to execute a service, the database matches the partition corresponding to p_name. If the partition does not exist, an exception is reported. If you specify p_value to execute a service, the database matches the partition to which p_value belongs.

gaussdb=# CREATE TABLE list_01
(
    id   INT,
    role VARCHAR(100),
    data VARCHAR(100)
)
PARTITION BY LIST (id)
(
    PARTITION p_list_1 VALUES(0,1,2,3,4),
    PARTITION p_list_2 VALUES(5,6,7,8,9),
    PARTITION p_list_3 VALUES(DEFAULT)
);

-- Cleanup example
gaussdb=# DROP TABLE list_01;

When partitions are specified, PARTITION p_list_1 and PARTITION FOR (4) are equivalent and refer to the same partition, and PARTITION p_list_3 and PARTITION FOR (12) are equivalent and refer to the same partition.