Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ SQL Syntax/ SQL Statements/ A/ ALTER TABLE PARTITION

Updated on 2025-10-23 GMT+08:00

View PDF

ALTER TABLE PARTITION

Description

Modifies table partitions, including adding, deleting, splitting, merging, clearing, exchanging, renaming, and collecting partitions, moving partition tablespaces, enabling and disabling the automatic partitioning function, and modifying partition attributes.

Precautions

Only the owner of a partitioned table or users granted with the ALTER permission can run the ALTER TABLE PARTITION command. When separation of duties is disabled, a system administrator has 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 column number in customized partition, and they must be consistent.
Hash partitioned tables do not support splitting, combination, addition, and deletion of partitions.
Deleting, splitting, merging, clearing, and exchanging partitions will invalidate the global index. The UPDATE GLOBAL INDEX clause can be used to update the index 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.
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.

Syntax

Modifying a partition in a partitioned table includes modifying the main syntax of the table partition and modifying the syntax of the table partition name.

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, M-compatible databases execute DROP PARTITION and ADD PARTITION first, and then the rest clauses in sequence.
```
    move_clause  |
    exchange_clause  |
    merge_clause  |
    split_clause  |
    add_clause  |
    drop_clause  |
    truncate_clause  |
    analyze_clause  |
```
- The move_clause syntax is used to move the partition to a new tablespace.
```
MOVE PARTITION { partition_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename
```
- The exchange_clause syntax is used to move the data from an ordinary table to a specified partition.
```
EXCHANGE PARTITION {  partition_name | FOR ( partition_value [, ... ] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ]  [ UPDATE GLOBAL INDEX ] 
```
  The ordinary table and partition whose data is to be exchanged must meet the following requirements:
  - The number of columns of 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 partitioned table 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 partitioned table should be consistent.
  - An ordinary table cannot be a temporary table. A partitioned table can only be a range partitioned table, list partitioned table, or hash partitioned table.
  When the exchange is done, the data and tablespace of the ordinary table and partitioned table are exchanged. In this case, the statistics on the ordinary table and partition 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 may fail to be exchanged.
  To exchange data, you can create an intermediate table. You need to insert the partition data into the intermediate table, truncate the partition, insert the ordinary table data into the partitioned table, drop the ordinary table, and rename the intermediate table to complete data exchange.
  
  If the DROP COLUMN operation is performed on an ordinary or a 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.
  
  EXCHANGE PARTITION partition_name:
  
  If partition_name is the name of a level-1 partition, the level-1 partition and ordinary table are exchanged. If partition_name is the name of a level-2 partition, the level-2 partition and ordinary table are exchanged.
  
  Level-1 partitions and ordinary tables cannot be exchanged in level-2 partitioned tables.
  
  After partitions are exchanged, auto-increment columns are not reset.
- 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 
     [ TABLESPACE tablespacename ] [ UPDATE GLOBAL INDEX ]
```
  For range 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.
  
  Ustore tables do not support ALTER TABLE MERGE PARTITIONS in transaction blocks and stored procedures.
- The split_clause syntax is used to split one partition into partitions.
```
SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause } [ UPDATE 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 [ TABLESPACE tablespacename ] , PARTITION partition_name [ 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. no_split_point_clause
    
    partition_less_than_item supports a maximum of 16 partition keys, while partition_start_end_item supports only one partition key. For details about the supported data types, see PARTITION BY RANGE [COLU....
    
    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 syntax of partition_less_than_item 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 } 
    [ TABLESPACE tablespacename ]
```
    During range partitioning, the MAXVALUE keyword can be used without parentheses. It cannot be used in subpartitions of level-2 partitions or in scenarios where the partition field contains multiple columns.
  - 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 | MAXVALUE})} |
        {START(partition_value)} |
        {END({partition_value | MAXVALUE})}
}  [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[ TABLESPACE tablespacename ] , PARTITION partition_name [ 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) [ TABLESPACE tablespacename ][, ...] )
```
    The range of the last partition 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({partition_less_than_item | partition_start_end_item | partition_list_item}[, ...])
```
  The partition_list_item syntax is as follows:
```
PARTITION partition_name VALUES [IN] (list_values_clause) 
    [ TABLESPACE tablespacename ]
```
  partition_list_item supports a maximum of 16 partition keys. For details about the supported data types, see PARTITIONBYLIST[COLUMNS](partition_key).
  
  Interval and hash partitioned tables do not support partition addition.
  
  IN 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
```
  - 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  { { ALL | partition_name [, ...] } | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]
```
- The analyze_clause syntax is used to collect statistics about a specified partition in a partitioned table.
```
ANALYZE PARTITION  { { ALL | partition_name [, ...] } 
```

The syntax for modifying the partition name is as follows:

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

Parameters

table_name
Specifies the name of a partitioned table.

Value range: an existing partitioned 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

Note: You need to create or delete tablespaces in a non-M-compatible database.
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.
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.
partition_new_name
Specifies the new name of a partition.

Value range: a string complying with Identifier Description.
{ ALL | partition_name [, ...] }
ALL: specifies all partition data.

partition_name: specifies a partition in the target partitioned table. Level-1 and level-2 partition names are supported.

Value range: an existing partition name.
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.

Examples

Change the name of a table partition.

-- Create a pre-partitioned table.
m_db=# 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.
m_db=# ALTER TABLE test_p1 RENAME PARTITION p3 TO pmax;

-- Query partition information.
m_db=# 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)

Exchange partitions.

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

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

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

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

Merge partitions.

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

-- View the partitions.
m_db=# 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 the table and tablespace.
m_db=# DROP TABLE test_p1;

Split partitions.

-- Create a table.
m_db=# 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.
m_db=# ALTER TABLE test_r1 SPLIT PARTITION pmax AT (20) INTO (PARTITION p2, PARTITION pmax);
m_db=# ALTER TABLE test_r1 SPLIT PARTITION pmax INTO (
    PARTITION p3   VALUES LESS THAN (30),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- Query.
m_db=# 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.
m_db=# DROP TABLE test_r1;

-- Create a table.
m_db=# 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.
m_db=# ALTER TABLE test_r2 SPLIT PARTITION pmax INTO (
    PARTITION p3 START(20) END(30),
    PARTITION pmax START(30) END (MAXVALUE)
);

-- Query.
m_db=# 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.
m_db=# DROP TABLE test_r2;

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

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

-- Query.
m_db=# 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.
m_db=# DROP TABLE test_l1;

Add partitions.

-- Create a table.
m_db=# 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.
m_db=# ALTER TABLE test_p2 ADD PARTITION (PARTITION p3 VALUES LESS THAN (30));

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

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

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

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

-- Enter the M mode.
-- Create a table.
m_db=# CREATE TABLE addpart1
(
   month_code int,
   num        int,
   name       varchar(30)
)PARTITION BY RANGE COLUMNS(month_code)
(
   PARTITION p_202301 VALUES LESS THAN(202302),
   PARTITION p_202302 VALUES LESS THAN (202303)
);
-- Add a partition.
m_db=# ALTER TABLE addpart1 ADD PARTITION (PARTITION p_202303 VALUES LESS THAN(202304));
-- Drop the table.
m_db=# DROP TABLE addpart1;

Drop partitions.

-- Create a table.
m_db=# 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.
m_db=# ALTER TABLE test_p4 DROP PARTITION p2;

-- Query.
m_db=# 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.
m_db=# DROP TABLE test_p4;

Truncate partitions.

-- Create a table.
m_db=# 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.
m_db=# INSERT INTO test_p5 VALUES (GENERATE_SERIES(1,9), 100);

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

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

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

-- Clear data in the p1 and p2 partitions.
m_db=# ALTER TABLE test_p5 TRUNCATE PARTITION p1,p2;

-- Clear all partition data.
m_db=# ALTER TABLE test_p5 TRUNCATE PARTITION ALL;

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

Collect partition statistics.

-- Create a level-1 partitioned table.
m_db=# CREATE TABLE sales (
    id INT,
    region VARCHAR(100),
    amount DECIMAL(10,2),
    sale_date DATE
)
PARTITION BY RANGE(sale_date) (
    PARTITION p0 VALUES LESS THAN (20100101),
    PARTITION p1 VALUES LESS THAN (20110101),
    PARTITION p2 VALUES LESS THAN (20120101),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- Insert data.
m_db=# INSERT INTO sales(sale_date) VALUES('2005-1-1'),('2010-1-1'),('2011-1-1'),('2012-1-1');
-- Collect partition statistics.
m_db=# ALTER TABLE sales ANALYZE PARTITION p0;
m_db=# ALTER TABLE sales ANALYZE PARTITION p1,p2;
m_db=# ALTER TABLE sales ANALYZE PARTITION all;
m_db=# DROP TABLE sales;

-- Create a level-2 partitioned table.
CREATE TABLE range_hash1 (id INT, purchased INT)
PARTITION BY RANGE(purchased)
SUBPARTITION BY HASH(purchased)
(
    PARTITION p0 VALUES LESS THAN (19900101)
    (
        SUBPARTITION s0,
        SUBPARTITION s1
    ),
    PARTITION p1 VALUES LESS THAN (20000101)
    (
        SUBPARTITION s2,
        SUBPARTITION s3
    ),
    PARTITION p2 VALUES LESS THAN MAXVALUE
    (
        SUBPARTITION s4,
        SUBPARTITION s5
    )
);
-- Insert data.
m_db=# insert into range_hash1 values(1,19800101),(2,19800601),(3,19850101),(4,19850601),(5,19900102);
-- Collect statistics on level-1 and level-2 partitions.
m_db=# ALTER TABLE range_hash1 ANALYZE PARTITION s0,s1;
m_db=# ALTER TABLE range_hash1 ANALYZE PARTITION p0,p1;
m_db=# ALTER TABLE range_hash1 ANALYZE PARTITION all;
m_db=# ALTER TABLE range_hash1 ANALYZE PARTITION p2,s4,s5;
m_db=# ALTER TABLE range_hash1 ANALYZE PARTITION all;
m_db=# DROP TABLE range_hash1;

Helpful Links

CREATE TABLE PARTITION and DROP TABLE

Parent topic: A

Previous topic: ALTER TABLE

Next topic: ALTER TABLE SUBPARTITION

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot