Pivotal Knowledge Base

Follow

How to Drop, Add and Rename a Partition Table

Environment

 Product  Version
 Pivotal Greenplum (GPDB)  4.3.x
 OS  RHEL 6.x
 Others  

Purpose

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

Procedure

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

flightdata=# \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
Now, we want to drop 1 of the partition tables for sales. We will drop the partition table "sales_1_prt_jan08".

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

As you can see below, the monthly partition table for Jan08 has been dropped:

flightdata=# \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)

flightdata=#

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

flightdata=# 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:

flightdata=# \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)

flightdata=#

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

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

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

flightdata=# \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)

flightdata=#

Additional Information

If you want to exchange a partition table you can follow the KB 203330727.
For more information see Partitioning Large Tables.

Comments

Powered by Zendesk