Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 12.2 ALTER TABLE...ADD PARTITION

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.2 ALTER TABLE...ADD PARTITION

Use the ALTER TABLEADD PARTITION command to add a partition to an existing partitioned table. The syntax is:

ALTER TABLE table_name ADD PARTITION partition_definition;

Where partition_definition is:

      {list_partition | range_partition }

and list_partition is:

      PARTITION partition_name

      VALUES (value[, value]...)

      [TABLESPACE tablespace_name]

      [(subpartition, ...)]

and range_partition is:

      PARTITION partition_name

      VALUES LESS THAN (value[, value]...)

      [TABLESPACE tablespace_name]

      [(subpartition, ...)]

Where subpartition is:

      {list_subpartition | range_subpartition}

and list_subpartition is:

      SUBPARTITION partition_name

      VALUES (value[, value]...)

      [TABLESPACE tablespace_name]

and range_subpartition is:

      SUBPARTITION partition_name

      VALUES LESS THAN (value[, value]...)

      [TABLESPACE tablespace_name]

Description

The ALTER TABLE… ADD PARTITION command adds a partition to an existing partitioned table. There is no upper limit to the number of defined partitions.

The new partition rules will reference the column specified in the rules that define the existing partition(s). If the existing partitions are defined using LIST rules, any new partitions added with the ADD PARTITION clause must also be defined using LIST rules. If the existing partitions use RANGE boundaries, any new partitions must also specify RANGE boundaries.

If the partitioned table is indexed, the index will be replicated on the new partition.

To use the ALTER TABLE... ADD PARTITION command you must be the table owner, or have superuser (or administrative) privileges.

Parameters

table_name

The name (optionally schema-qualified) of the modified table.

partition_name

The name of the partition to be created. Partition names must be unique amongst all partitions and subpartitions, and must follow the naming conventions for object identifiers.

subpartition_name

The name of the subpartition to be created. Subpartition names must be unique amongst all partitions and subpartitions, and must follow the naming conventions for object identifiers.

column

The name of a column on which the partitioning rules are based. The value stored in the specified column will determine which partition that the record will be stored in.

(value[, value]...)

Use value to specify a quoted literal value (or comma-delimited list of literal values) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but there is no limit placed on the number of values specified within a rule. value may be NULL, DEFAULT (if specifying a LIST partition), or MAXVALUE (if specifying a RANGE partition).

      When specifying rules for a LIST partitioned table, include the DEFAULT keyword in the last partition rule to direct any un-matched records to the given partition. If you do not include a rule that includes a value of DEFAULT, any INSERT statement that attempts to add a record that does not match the specified rules of at least one partition will fail, and return an error.

      When specifying rules for a RANGE partitioned table, include the MAXVALUE keyword in the last partition rule to direct any un-categorized records to the given partition. If you do not include a rule that includes a value of MAXVALUE, any INSERT statement that attempts to add a record with a value greater than the highest value specified will fail, and return an error.

tablespace_name

The name of the tablespace in which the partition or subpartition resides.

Example - Adding a partition to a LIST partitioned table.

The example that follows adds a partition to the LIST partitioned version of the employees table. The table was created using the command:

CREATE TABLE employees

(

empno numeric(4,0),

ename varchar(10),

job varchar(9),

mgr numeric(4,0),

hiredate timestamp,

sal numeric(7,2),

comm numeric(7,2),

deptno numeric(7,2)

)

PARTITION BY LIST (job)

(

PARTITION sales VALUES ('SALESMAN', 'CLERK'),

PARTITION support VALUES ('ANALYST', 'DEFAULT')

);

The following command demonstrates adding a partition named mgmt to the employees table:

ALTER TABLE employees ADD PARTITION mgmt VALUES ('MANAGER', 'PRESIDENT');

After executing the command, the table will have three partitions - sales, support and mgmt.

Example - Adding a partition to a RANGE partitioned table.

The example adds a partition to the RANGE partitioned version of the employees table. The table was created using the command:

CREATE TABLE employees

(

empno numeric(4,0),

ename varchar(10),

job varchar(9),

mgr numeric(4,0),

hiredate timestamp,

sal numeric(7,2),

comm numeric(7,2),

deptno numeric(7,2)

)

PARTITION BY RANGE (hiredate)

(

PARTITION ten_year_staff VALUES LESS THAN('01-JAN-2001'),

PARTITION five_year_staff VALUES LESS THAN('01-JAN-2006')

);

Use the following command to add a third partition (one_year_staff) that stores employee records for those employees with a hiredate that falls between January 1, 2006 and December 31, 2010:

ALTER TABLE employees ADD PARTITION one_year_staff
VALUES LESS THAN('01-JAN-2011');

After executing the command, the employees table will have three partitions - one_year_staff, five_year_staff and ten_year_staff.

Previous PageTable Of ContentsNext Page