ALTER TABLE PARTITION

Description

Modifies table partitions, including adding, deleting, splitting, merging, clearing, exchanging, and renaming partitions, moving partition tablespaces, and modifying partition attributes.

Precautions

Only the partitioned table owner or a user granted with the ALTER permission can run the ALTER TABLE PARTITION command. When separation of duties is disabled, system administrators have this permission by default.
The tablespace of the added partition cannot be PG_GLOBAL.
The name of the added partition must be different from names of existing partitions in the partitioned table.
The key value of the added partition must be consistent with the type of partition keys in the partitioned table.
If a range partition is added, the key value of the added partition must be greater than the upper limit of the last range partition in the partitioned table.
If a list partition is added, the key value of the added partition cannot be the same as that of an existing partition.
Hash partitions cannot be added.
If the number of partitions in the target partitioned table has reached the maximum (1048575), partitions cannot be added.
If a partitioned table has only one partition, the partition cannot be deleted.
Use PARTITION FOR() to choose partitions. The number of specified values in the brackets should be the same as the number of columns specified when you define a partition, and they must be consistent.
The Value partitioned table does not support the ALTER PARTITION operation.
Hash partitioned tables do not support splitting, combination, addition, or deletion of partitions.
Deleting, splitting, merging, clearing, and exchanging partitions will invalidate global indexes. The UPDATE GLOBAL INDEX clause can be used to update the indexes synchronously.
If the UPDATE GLOBAL INDEX clause is not used when you delete, split, merge, clear, or exchange partitions, concurrent DML services may report errors due to invalidated indexes.
Deleting, splitting, merging, clearing, or exchanging partitions invalidates GSIs on partitioned tables. For exchanging partitions, all GSIs on ordinary tables become invalid. You can declare the UPDATE DISTRIBUTED GLOBAL INDEX clause to update indexes synchronously.
If the UPDATE DISTRIBUTED GLOBAL INDEX clause is not used when you delete, split, merge, clear, or exchange partitions, concurrent DML services may report errors due to invalidated indexes.
If enable_gpi_auto_update is set to on, the global index is automatically updated even if the UPDATE GLOBAL INDEX clause is not declared.
If you add a row-level expression when adding or changing an ILM policy for a data object, note that the row-level expression supports only the functions listed in the whitelist. For details about the whitelist function list, see Row Expression Function Whitelist.

Syntax

Modifying a partition in a partitioned table includes modifying the table partition itself and the table partition name, and resetting the partition ID.

Modify the syntax of the table partition.

      
           ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];

action indicates the following clauses for maintaining partitions. For the partition continuity when multiple clauses are used for partition maintenance, GaussDB executes DROP PARTITION and ADD PARTITION first, and then the rest clauses in sequence.

       
            move_clause  |
    exchange_clause  |
    row_clause  |
    merge_clause  |
    modify_clause  |
    split_clause  |
    add_clause  |
    drop_clause  |
    truncate_clause  |
    ilm_clause

The move_clause syntax is used to move the partition to a new tablespace.

         
              MOVE PARTITION { partion_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename

The exchange_clause syntax is used to move the data from a general table to a specified partition.

         
              EXCHANGE PARTITION { ( partition_name ) | partition_name | FOR ( partition_value [, ...] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ] [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

The ordinary table and partition whose data is to be exchanged must meet the following requirements:

The number of columns in the ordinary table is the same as that of the partition, and their information should be consistent, including: column name, data type, constraint, collation information, storage parameter, and compression information.
The compression information of the ordinary table and partition should be consistent.
The distribution key information of the ordinary table and partition should be consistent.
The number of ordinary table indexes is the same as that of local indexes of the partition, and the index information is the same.
The number and information of constraints of the ordinary table and partition should be consistent.
An ordinary table cannot be a temporary table. A partitioned table can only be a range partitioned table, list partitioned table, hash partitioned table, or interval partitioned table.
When the built-in security policy is enabled, common and partitioned tables cannot have dynamic data masking or row-level security constraints.

When the exchange is done, the data and tablespace of the ordinary table and partition are exchanged. The statistics about ordinary tables and partitions become unreliable, and they should be analyzed again.
A non-partition key cannot be used to create a local unique index. Therefore, if an ordinary table contains a unique index, data cannot be exchanged.
To exchange data, you can create an intermediate table, insert partition data into the intermediate table, truncate partitions, insert ordinary table data into the partitioned table, drop the ordinary table, and rename the intermediate table.
In a scenario where both an ordinary table and a partitioned table are Ustore tables, if the UB-tree index type (RCR or PCR, RCR by default) of the ordinary table is different from the local UB-tree index type (RCR or PCR, RCR by default) of the partitioned table, data cannot be exchanged.
If the DROP COLUMN operation is performed on an ordinary or partitioned table, the deleted column still exists physically. Therefore, you need to ensure that the deleted column of the ordinary table is strictly aligned with that of the partition. Otherwise, the exchange will fail.
The EXCHANGE PARTITION { ( partition_name ) | partition_name | FOR ( partition_value [, ... ] ) } operation is available in B-compatible database (that is, sql_compatibility set to 'MYSQL'). In other modes, only the EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ... ] ) } operation is available. If partition_name is the name of a level-1 partition, the level-1 partition and ordinary table are exchanged.

The row_clause syntax is used to set row movement of a partitioned table.

         
              { ENABLE | DISABLE } ROW MOVEMENT

The merge_clause syntax is used to merge partitions into one. The maximum number of source partitions that can be merged in a command is 300.
```
MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name 
    [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]
```
- For range partitioning and interval partitioning, the ranges of the source partitions must increase continuously, and the partition name after MERGE can be the same as the name of the last source partition. For list partitioning, there is no such range requirement on the source partitions, and the partition name after MERGE can be the same as that of any source partition. If the partition name after MERGE is the same as that of a source partition, they are considered as the same partition.
- If the GUC parameter enable_ilm is not enabled and the merge_clause syntax is used to merge multiple partitions with the ILM policy into one partition, the new partition does not inherit the ILM policy.
Ustore tables do not support ALTER TABLE MERGE PARTITIONS in transaction blocks and stored procedures.

The modify_clause syntax is used to set whether a partitioned index is usable.

         
              MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

The split_clause syntax is used to split one partition into multiple ones.

         
              SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause } [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

The partition name after SPLIT can be the same as the source partition name, but they are regarded as different partitions.
If the GUC parameter enable_ilm is not enabled and the split_clause syntax is used to split a partition with the ILM policy into multiple partitions, the new partitions do not inherit the ILM policy.

The split_point_clause syntax for specifying the split point for a range partitioned table is as follows:

           
                AT ( partition_value ) INTO ( PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] , PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] )

The size of the split point should be in the range of partition keys of the partition to be split. The split point can only split one partition into two new partitions.

The no_split_point_clause syntax without specifying a split point for a range partitioned table is as follows:

           
                INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }

The first new partition key specified by partition_less_than_item should be greater than that of the previously split partition (if any), and the last partition key specified by partition_less_than_item should equal that of the partition being split.
The first new partition key specified by partition_start_end_item should equal that of the former partition (if any), and the last partition key specified by partition_start_end_item should equal that of the partition being split.
partition_less_than_item supports a partition key with up to 16 columns, while partition_start_end_item supports a one-column partition key. For details about the supported data types, see PARTITION BY RANGE(partition_key).
partition_less_than_item and partition_start_end_item cannot be used in the same statement. There is no restriction on different SPLIT statements.

The partition_less_than_item syntax is as follows (the range of the last partition is not defined, that is, the VALUES LESS THAN (partition_value) part is not defined; by default, the last partition inherits the upper boundary value of the range defined for the source partition):

           
                PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE }  [, ...] ) | MAXVALUE }
    [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]

During range partitioning, the MAXVALUE keyword can be used without parentheses. It can be used only in MySQL-compatible mode. It cannot be used in subpartitions of level-2 partitions or in scenarios where multi-column partition keys are used.

The partition_start_end_item syntax is as follows. For details about the constraints, see START END.

           
                PARTITION partition_name {
        {START(partition_value) END (partition_value) EVERY (interval_value)} |
        {START(partition_value) END ({partition_value | MAXVALUE})} |
        {START(partition_value)} |
        {END({partition_value | MAXVALUE})}
} [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [TABLESPACE tablespace_name]

The split_point_clause syntax for specifying the split point for a list partitioned table is as follows:

VALUES ( partition_value_list ) INTO ( PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ] , PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ] )

The split point must be a non-empty true subset of the source partition. Specifying a split point can only split one partition into two partitions.

The no_split_point_clause syntax for not specifying the split point for a list partitioned table is as follows:
```
INTO ( PARTITION partition_name VALUES (partition_value_list) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ][, ...] )
```
- The range of the last partition is not defined, that is, the VALUES (partition_value_list) part is not defined; the partition range is equal to the remaining set of the source partition excluding other level-2 partitions.
- If no split point is specified, each new partition must be a non-empty true subset of the source partition and does not overlap with each other.

The add_clause syntax is used to add one or more partitions to a specified partitioned table.

         
              ADD {{partition_less_than_item | partition_start_end_item} |
     PARTITION({partition_less_than_item | partition_start_end_item | partition_list_item})}

The PARTITION({partition_less_than_item | partition_start_end_item | partition_list_item}) syntax can be used only in MySQL-compatible mode.
The ALTER TABLE table_name ADD PARTITION (partition_definition1, partition_definition2,…); syntax cannot be used to add multiple partitions. Only the original syntax for adding multiple partitions is supported: ALTER TABLE table_name ADD PARTITION (partition_definition1), ADD PARTITION (partition_definition2), …

The partition_list_item syntax is as follows:

PARTITION partition_name VALUES [IN] (list_values_clause) 
    [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]

partition_list_item supports a maximum of 16 partition keys. For details about the supported data types, see PARTITION BY LIST [COLUMNS] (partition_key).

Partitions cannot be added to a hash partitioned table.
IN needs to be used in MySQL-compatible mode and cannot be used in subpartitions of level-2 partitions.

The drop_clause syntax is used to remove a partition from a specified partitioned table.

        
             DROP PARTITION  { partition_name | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

Hash partitioned table does not support partition deletion.
If a partitioned table has only one partition, the partition cannot be deleted.

The truncate_clause syntax is used to remove a specified partition from a partitioned table.

TRUNCATE PARTITION  { partition_name | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

The ilm_clause syntax adds an ILM policy to a partition. It supports the syntax for the OLTP table compression feature of data lifecycle management.

MODIFY PARTITION partition_name ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]

The syntax for modifying the name of a partition is as follows:

      
           ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

Reset a partition ID.

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )} RESET PARTITION;

Parameters

table_name
Specifies the name of a partitioned table.

Value range: an existing table name
partition_name
Specifies the name of a partition.

Value range: an existing partition name
tablespacename
Specifies which tablespace the partition moves to.

Value range: an existing tablespace name
partition_value
Specifies the key value of a partition.

The value specified by PARTITION FOR ( partition_value [, ...] ) can uniquely identify a partition.

Value range: partition keys for the partition to be operated.
UNUSABLE LOCAL INDEXES
Sets all the indexes unusable in the partition.
REBUILD UNUSABLE LOCAL INDEXES
Rebuilds all the indexes in the partition.
{ ENABLE | DISABLE } ROW MOVEMET
Sets row movement.

If the tuple value is updated on the partition key during the UPDATE operation, the partition where the tuple is located is altered. Setting this parameter enables error messages to be reported or movement of the tuple between partitions.

Value range:
- ENABLE: Row movement is enabled.
- DISABLE: Row movement is disabled.
By default, this parameter is disabled.
ordinary_table_name
Specifies the name of the ordinary table whose data is to be migrated.

Value range: an existing ordinary table name.
{ WITH | WITHOUT } VALIDATION
Checks whether the ordinary table data meets the specified partition key range of the partition to be migrated.

Value range:
- WITH: checks whether the ordinary table data meets the partition key range of the partition to be migrated. If any data does not meet the required range, an error is reported.
- WITHOUT: does not check whether the ordinary table data meets the partition key range of the partition to be migrated.
The default value is WITH.

The check is time consuming, especially when the data volume is large. Therefore, use WITHOUT when you are sure that the current ordinary table data meets the partition key range of the partition to be migrated.
VERBOSE
When VALIDATION is WITH, if the ordinary table contains data that is out of the partition key range, insert the data to the correct partition. If there is no correct partition where the data can be inserted to, an error is reported.

Only when VALIDATION is WITH, VERBOSE can be specified.
partition_new_name
Specifies the new name of a partition.

Value range: a string. It must comply with the naming convention.
UPDATE GLOBAL INDEX
Updates all global indexes in the partitioned table to ensure that correct data can be queried using global indexes.

If this parameter is not used, all global indexes in the partitioned table become invalid.
UPDATE DISTRIBUTED GLOBAL INDEX
Updates all available global secondary indexes in the partitioned table to ensure that the data in the global secondary indexes is consistent with that in the base table. EXCHANGE PARTITION also updates all available global secondary indexes in the ordinary table.

If this parameter is not used, all global secondary indexes in the partitioned table become invalid. EXCHANGE PARTITION also invalidates all global secondary indexes in the ordinary table.
NO UPDATE DISTRIBUTED GLOBAL INDEX
If this parameter is used, all GSIs in the partitioned table become invalid. EXCHANGE PARTITION also invalidates all global secondary indexes in the ordinary table.

Examples

Change the name of a table partition.

-- Create a pre-partitioned table.
gaussdb=# CREATE TABLE test_p1 (col1 INT, col2 INT) PARTITION BY RANGE (col1) 
( 
    PARTITION p1 VALUES LESS THAN (10), 
    PARTITION p2 VALUES LESS THAN (20), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
);

-- Change the partition name.
gaussdb=# ALTER TABLE test_p1 RENAME PARTITION p3 TO pmax;

-- Query partition information.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r';
 relname | boundaries |  oid  
---------+------------+-------
 p1      | {10}       | 17066
 p2      | {20}       | 17067
 pmax    | {NULL}     | 17068
(3 rows)

Move partition tablespaces.

-- Create a partition.
gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';

-- Move partition tablespaces.
gaussdb=# ALTER TABLE test_p1 MOVE PARTITION P1 TABLESPACE tbs_data1;

-- Check the partition tablespace.
gaussdb=# SELECT relname, spcname FROM pg_partition t1, pg_tablespace t2 WHERE T1.reltablespace=t2.oid and t1.parentid='test_p1'::regclass;
 relname |  spcname  
---------+-----------
 p1      | tbs_data1
(1 row)

Exchange partitions.

-- Create an ordinary table and insert data into the table.
gaussdb=# CREATE TABLE test_ep1(col1 INT,col2 INT);
gaussdb=# INSERT INTO test_ep1 VALUES (GENERATE_SERIES(1,30), 1000);

-- Migrate ordinary table data to a specified partition.
gaussdb=# ALTER TABLE test_p1 EXCHANGE PARTITION (p1) WITH TABLE test_ep1 VERBOSE;

-- Query.
gaussdb=# SELECT COUNT(*) FROM test_p1 PARTITION (p1);
 count 
-------
     9
(1 row)

-- Delete the test_ep1 table.
gaussdb=# DROP TABLE test_ep1;

Merge partitions.

-- Merge the p2 and pmax partitions in the test_p1 table into pmax.
gaussdb=# ALTER TABLE test_p1 MERGE PARTITIONS p2,pmax INTO PARTITION pmax;

-- View a partition.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r' order by 1;
 relname | boundaries |  oid  
---------+------------+-------
 p1      | {10}       | 17066
 pmax    | {NULL}     | 17070
(2 rows)

-- Delete tables and tablespaces.
gaussdb=# DROP TABLE test_p1;
gaussdb=# DROP TABLESPACE tbs_data1;

Split partitions.

-- Create a table.
gaussdb=# CREATE TABLE test_r1 (col1 INT,col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1   VALUES LESS THAN (10),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
); 

-- Split partitions.
gaussdb=# ALTER TABLE test_r1 SPLIT PARTITION pmax AT (20) INTO (PARTITION p2, PARTITION pmax);
gaussdb=# ALTER TABLE test_r1 SPLIT PARTITION pmax INTO (
    PARTITION p3   VALUES LESS THAN (30),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- Query.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r1'::regclass AND parttype <> 'r' order by 1;
 relname | boundaries |  oid  
---------+------------+-------
 p1      | {10}       | 17088
 p2      | {20}       | 17090
 p3      | {30}       | 17092
 pmax    | {NULL}     | 17093
(4 rows)

-- Delete the test_r1 table.
gaussdb=# DROP TABLE test_r1;

-- Create a table.
gaussdb=# CREATE TABLE test_r2(col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1   START(1) END(10),
    PARTITION p2   START(10) END(20),
    PARTITION pmax START(20) END(MAXVALUE)
);

-- Split partitions.
gaussdb=# ALTER TABLE test_r2 SPLIT PARTITION pmax INTO (
    PARTITION p3 START(20) END(30),
    PARTITION pmax START(30) END (MAXVALUE)
);

-- Query.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r2'::regclass AND parttype <> 'r' order by 1;
 relname | boundaries |  oid  
---------+------------+-------
 p1_0    | {1}        | 17112
 p1_1    | {10}       | 17113
 p2      | {20}       | 17114
 p3      | {30}       | 17116
 pmax    | {NULL}     | 17117
(5 rows)

-- Delete the test_r2 table.
gaussdb=# DROP TABLE test_r2;

-- Create a table.
gaussdb=# CREATE TABLE test_l1(col1 INT, col2 INT) PARTITION BY LIST(col1)(
    PARTITION p1   VALUES (10,20),
    PARTITION p2 VALUES (30,40)
);

-- Split partitions.
gaussdb=# ALTER TABLE test_l1 SPLIT PARTITION p1 VALUES (10) INTO (PARTITION p1_1, PARTITION p1_2);
gaussdb=# ALTER TABLE test_l1 SPLIT PARTITION p2 INTO (PARTITION p3_1 VALUES(30), PARTITION p3_2);

-- Query.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_l1'::regclass AND parttype <> 'r' order by 1;
 relname | boundaries |  oid  
---------+------------+-------
 p1_1    | {10}       | 17132
 p1_2    | {20}       | 17133
 p3_1    | {30}       | 17134
 p3_2    | {40}       | 17135
(4 rows)

-- Delete the test_l1 table.
gaussdb=# DROP TABLE test_l1;

Add partitions.

-- Create a table.
gaussdb=# CREATE TABLE test_p2 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (20)
);

-- Add a partition.
gaussdb=# ALTER TABLE test_p2 ADD PARTITION p3 VALUES LESS THAN (30);

-- Delete the test_p2 table.
gaussdb=# DROP TABLE test_p2;

-- Create a table.
gaussdb=# CREATE TABLE test_p3 (col1 INT, col2 INT) PARTITION BY LIST(col1)(
    PARTITION p1 VALUES (1),
    PARTITION p2 VALUES (2)
);

-- Add a partition.
gaussdb=# ALTER TABLE test_p3 ADD PARTITION p3 VALUES (3);

-- Delete the test_p3 table.
gaussdb=# DROP TABLE test_p3;

Drop partitions.

-- Create a table.
gaussdb=# CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY LIST(col1)(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2));

-- Delete the p2 partition from the test_p3 table.
gaussdb=# ALTER TABLE test_p4 DROP PARTITION p2;

-- Query.
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p4'::regclass;
 relname | boundaries |  oid  
---------+------------+-------
 test_p4 |            | 17187
 p1      | {1}        | 17188
(2 rows)

-- Delete the test_p4 table.
gaussdb=# DROP TABLE test_p4;

-- Specify the partition value to delete a partition.
-- Create a table.
gaussdb=# CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY RANGE(col1)(PARTITION p1 VALUES LESS THAN(1),PARTITION p2 VALUES LESS THAN (2));

-- Delete the partition whose partition key is 1 from the test_p3 table.
gaussdb=# ALTER TABLE test_p4 DROP PARTITION FOR (1);

-- Query.
gaussdb=# SELECT relname, boundaries FROM pg_partition WHERE parentid='test_p4'::regclass order by 1 desc;
 relname | boundaries
---------+------------
 test_p4 |
 p1      | {1}
(2 rows)

-- Delete the test_p4 table.
gaussdb=# DROP TABLE test_p4;

Clear partitions.

-- Create a table.
gaussdb=# CREATE TABLE test_p5 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10)
);

-- Insert data.
gaussdb=# INSERT INTO test_p5 VALUES (GENERATE_SERIES(1,9), 100);

-- View data in the p2 partition.
gaussdb=# SELECT * FROM test_p5 PARTITION (p2);
 col1 | col2 
------+------
    5 |  100
    6 |  100
    7 |  100
    8 |  100
    9 |  100
(5 rows)

-- Clear the data in the p2 partition.
gaussdb=# ALTER TABLE test_p5 TRUNCATE PARTITION p2;

-- View data in the p2 partition.
gaussdb=# SELECT * FROM test_p5 PARTITION (p2);
 col1 | col2 
------+------
(0 rows)

-- Delete the test_p5 table.
gaussdb=# DROP TABLE test_p5;

Helpful Links

CREATE TABLE PARTITION and DROP TABLE

ALTER TABLE PARTITION

Description

Precautions

Syntax

Parameters

Examples

Helpful Links

Feedback

Was this page helpful?