How to Drop, Add, and Rename a Partition Table

Products

• VMware Tanzu Greenplum

Issue/Introduction

The purpose of this article is to explain the procedures of How to Drop, Add and Rename a Partition Table.

 

Environment

Resolution

For this example, we have a sales table split into monthly partitions:

db01=# \d+ sales
                    Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Child tables: sales_1_prt_apr08,
              sales_1_prt_aug08,
              sales_1_prt_dec08,
              sales_1_prt_feb08,
              sales_1_prt_jan08,
              sales_1_prt_jul08,
              sales_1_prt_jun08,
              sales_1_prt_mar08,
              sales_1_prt_may08,
              sales_1_prt_nov08,
              sales_1_prt_oct08,
              sales_1_prt_sep08
Has OIDs: no
Distributed by: (id)
Partition by: (date)

Drop

NOTE: Dropping a partiton will drop/delete all data within the partition

db01=# alter table sales drop partition jan08;
ALTER TABLE

As you can see below, the monthly partition table for Jan08 has been dropped. All data that was contained in the partition has also been dropped/deleted.

db01=# \d+ sales
                    Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Child tables: sales_1_prt_apr08,
              sales_1_prt_aug08,
              sales_1_prt_dec08,
              sales_1_prt_feb08,
              sales_1_prt_jul08,
              sales_1_prt_jun08,
              sales_1_prt_mar08,
              sales_1_prt_may08,
              sales_1_prt_nov08,
              sales_1_prt_oct08,
              sales_1_prt_sep08
Has OIDs: no
Distributed by: (id)
Partition by: (date)

Add

To add the jan08 partition back to the Sales table, run the following command:

db01=# alter table sales add partition jan08 START (date '2008-01-01') inclusive end (date '2008-01-31');
NOTICE:  CREATE TABLE will create partition "sales_1_prt_jan08" for table "sales"
ALTER TABLE

As you can see from the output below, the partition table for jan08 has been added back.

NOTE: The partition will be empty and no contain any data

db01=# \d+ sales
                    Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Child tables: sales_1_prt_apr08,
              sales_1_prt_aug08,
              sales_1_prt_dec08,
              sales_1_prt_feb08,
              sales_1_prt_jan08,
              sales_1_prt_jul08,
              sales_1_prt_jun08,
              sales_1_prt_mar08,
              sales_1_prt_may08,
              sales_1_prt_nov08,
              sales_1_prt_oct08,
              sales_1_prt_sep08
Has OIDs: no
Distributed by: (id)
Partition by: (date)

Rename

To rename a partition table you can run the following command:

db01=# alter table sales rename partition jan08 to jan08bkp;
ALTER TABLE

From the below output you can see that the Jan08 table has been renamed to jan08bkp:

db01=# \d+ sales
                    Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Child tables: sales_1_prt_apr08,
              sales_1_prt_aug08,
              sales_1_prt_dec08,
              sales_1_prt_feb08,
              sales_1_prt_jan08bkp,
              sales_1_prt_jul08,
              sales_1_prt_jun08,
              sales_1_prt_mar08,
              sales_1_prt_may08,
              sales_1_prt_nov08,
              sales_1_prt_oct08,
              sales_1_prt_sep08
Has OIDs: no
Distributed by: (id)
Partition by: (date)

Additional Information

To exchange a partition table you can follow the article How to exchange partitions and sub-partitions

For more information see Partitioning Large Tables.