ALTER TABLE...SPLIT SUBPARTITION v16

Use the ALTER TABLE… SPLIT SUBPARTITION command to divide a single subpartition into two subpartition and redistribute the subpartition's contents. The command comes in two variations.

The first variation splits a range subpartition into two subpartitions:

ALTER TABLE <table_name> SPLIT SUBPARTITION <subpartition_name>
  AT (range_part_value)
  INTO
  (
    SUBPARTITION <new_subpart1>
      [TABLESPACE <tablespace_name>],
    SUBPARTITION <new_subpart2>
      [TABLESPACE <tablespace_name>]
  );

The second variation splits a list subpartition into two subpartitions:

ALTER TABLE <table_name> SPLIT SUBPARTITION <subpartition_name>
  VALUES (<value>[, <value>]...)
  INTO
  (
    SUBPARTITION <new_subpart1>
      [TABLESPACE <tablespace_name>],
    SUBPARTITION <new_subpart2>
      [TABLESPACE <tablespace_name>]
  );

Description

The ALTER TABLE...SPLIT SUBPARTITION command adds a subpartition to an existing subpartitioned table. There's no upper limit to the number of defined subpartitions. When you execute an ALTER TABLE...SPLIT SUBPARTITION command, EDB Postgres Advanced Server creates two subpartitions. It moves any rows that contain values that are constrained by the specified subpartition rules into new_subpart1 and any remaining rows into new_subpart2.

The new subpartition rules must reference the column specified in the rules that define the existing subpartitions.

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

If the table is indexed, the index is created on the new subpartition.

To use the ALTER TABLE... SPLIT SUBPARTITION 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.

subpartition_name

The name of the subpartition that's being split.

new_subpart1

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

new_subpart1 receives the rows that meet the subpartitioning constraints specified in the ALTER TABLE… SPLIT SUBPARTITION command.

new_subpart2

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

new_subpart2 receives the rows that aren't directed to new_subpart1 by the subpartitioning constraints specified in the ALTER TABLE… SPLIT SUBPARTITION command.

(value[, value]...)

Use value to specify a quoted literal value or comma-delimited list of literal values by which table entries are grouped 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 subpartition, or MAXVALUE if specifying a RANGE subpartition.

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

tablespace_name

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

• On this page
• Description
• Parameters