Modify partition and subpartition bounds - PolarDB

ALTER TABLE ... MODIFY PARTITION and MODIFY SUBPARTITION let you add or remove values from the value list of a partition or subpartition in PolarDB for PostgreSQL (Compatible with Oracle).

Syntax

Modify a partition

ALTER TABLE <table_name> MODIFY PARTITION <partition_name>
  { ADD VALUES (<value_list>) | DROP VALUES (<value_list>) }

Modify a subpartition

ALTER TABLE <table_name> MODIFY SUBPARTITION <subpartition_name>
  { ADD VALUES (<value_list>) | DROP VALUES (<value_list>) }

ADD VALUES adds one or more values to the partition's value list. DROP VALUES removes one or more values from the partition's value list.

Example

The following example creates a list-partitioned table, then adds and removes a value from one of its partitions.

Create the table:

CREATE TABLE q1_sales_by_region
      (deptno number,
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Add a value to the partition:

-- Add 'VV' to the q1_southcentral partition
ALTER TABLE q1_sales_by_region MODIFY PARTITION q1_southcentral ADD VALUES ( 'VV' );

Remove a value from the partition:

-- Remove 'VV' from the q1_southcentral partition
ALTER TABLE q1_sales_by_region MODIFY PARTITION q1_southcentral DROP VALUES ( 'VV' );