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:
The second form splits a LIST
partition into two partitions:
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.
example_splitting_a_list_partition example_splitting_a_range_partition example_splitting_a_range_list_partition
- On this page
- Description
- Parameters