ALTER TABLE...SPLIT PARTITION v16

Use the ALTER TABLE… SPLIT PARTITION command to divide a single partition into two partitions. This command maintains the partitioning of the original table in the newly created partitions and redistributes the partition's contents between the new partitions. The command syntax comes in two forms.

The first form splits a RANGE partition into two partitions:

ALTER TABLE <table_name> SPLIT PARTITION <partition_name>
  AT (<range_part_value>)
  INTO
  (
    PARTITION <new_part1>
     [TABLESPACE <tablespace_name>]
     [SUBPARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ],
    PARTITION <new_part2>
     [TABLESPACE <tablespace_name>]
     [SUBPARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]
  );

The second form splits a LIST partition into two partitions:

ALTER TABLE <table_name> SPLIT PARTITION <partition_name>
  VALUES (<value>[, <value>]...)
  INTO
  (
    PARTITION <new_part1>
     [TABLESPACE <tablespace_name>]
     [SUBPARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ],
    PARTITION <new_part2>
     [TABLESPACE <tablespace_name>]
     [SUBPARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]
  );

Description

The ALTER TABLE...SPLIT PARTITION command adds a partition to an existing LIST or RANGE partitioned table. The ALTER TABLE… SPLIT PARTITION command can't add a partition to a HASH partitioned table. There's no upper limit to the number of partitions.

When you execute an ALTER TABLE...SPLIT PARTITION command, EDB Postgres Advanced Server:

• Creates two new partitions
• Maintains the partitioning of the original table in the newly created partitions
• Redistributes the content of the old partition between them, as constrained by the partitioning rules

Include the TABLESPACE clause to specify the tablespace in which a partition resides. If you don't specify a tablespace, the partition resides in the tablespace of the original partitioned table.

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... SPLIT 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 that's being split.

new_part1

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

new_part1 receives the rows that meet the partitioning constraints specified in the ALTER TABLE… SPLIT PARTITION command.

new_part2

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

new_part2 receives the rows aren't directed to new_part1 by the partitioning constraints specified in the ALTER TABLE… SPLIT PARTITION command.

range_part_value

Use range_part_value to specify the boundary rules by which to create the new partition. The partitioning rule must contain at least one column of a data type that has two operators, that is, a greater-than-or-equal-to operator and a less-than operator. Range boundaries are evaluated against a LESS THAN clause and are non-inclusive. A date boundary of January 1, 2010 includes only those date values that fall on or before December 31, 2009.

(value[, value]...)

Use value to specify a quoted literal value or comma-delimited list of literal values by which to distribute rows into partitions. Each partitioning rule must specify at least one value. There's no limit on the number of values specified in a rule.

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 subpartitions. Use the clause 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 the partition or subpartition resides.

• On this page
• Description
• Parameters