Pivotal Knowledge Base

Follow

How to Drop, Add, and Rename a Partition Table

Environment

  • Pivotal Greenplum (GPDB) 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.x

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

  • Avatar
    vasista krishna

    Hi,

    is this possible if we are having default partition too.

    Thanks
    Vasista

Powered by Zendesk