ALTER TABLE...ADD PARTITION v16

Use the ALTER TABLE… ADD 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> | <hash_subpartition>}

and list_subpartition is:

        SUBPARTITION [<subpartition_name>]
          VALUES (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]

and range_subpartition is:

        SUBPARTITION [<subpartition_name>]
          VALUES LESS THAN (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]

and hash_subpartition is:

        SUBPARTITION [<subpartition_name>]
          [TABLESPACE <tablespace_name>] |
        SUBPARTITIONS <num> [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]

Description

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

New partitions must be of the same type (LIST, RANGE, or HASH) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partitions.

You can use the ALTER TABLE… ADD PARTITION statement to add a partition to a table with a DEFAULT rule. However, there can't be conflicting values between existing rows in the table and the values of the partition to add.

You can't use the ALTER TABLE… ADD PARTITION statement to add a partition to a table with a MAXVALUE rule.

You can alternatively use the ALTER TABLE… SPLIT PARTITION statement to split an existing partition, effectively increasing the number of partitions in a table.

Specify RANGE partitions in ascending order. You can't add a new partition that precedes existing partitions in a RANGE partitioned table.

Include the TABLESPACE clause to specify the tablespace in which the new partition resides. If you don't specify a tablespace, the partition resides in the default tablespace.

Use the STORE IN clause to specify the tablespace list across which the autogenerated subpartitions are stored.

If the table is indexed, the index is created 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 partitioned table.

partition_name

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

subpartition_name

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

(value[, value]...)

Use value to specify a quoted literal value or comma-delimited list of literal values by which rows are distributed into partitions. Each partitioning rule must specify at least one value. There's no limit on the number of values specified in a rule. value can also be NULL, DEFAULT if specifying a LIST partition, or MAXVALUE if specifying a RANGE partition.

For information about creating a DEFAULT or MAXVALUE partition, see Handling stray values in a LIST or RANGE partitioned table.

num

The SUBPARTITIONS num clause is supported only for HASH and can be used to specify the number of hash subpartitions. Alternatively, you can use the subpartition definition to specify individual subpartitions and their tablespaces. If you don't specify SUBPARTITIONS or SUBPARTITION DEFINITION, then the partition creates a subpartition based on the SUBPARTITION TEMPLATE.

tablespace_name

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

• On this page
• Description
• Parameters