A DEFAULT or MAXVALUE partition or subpartition captures any rows that don't meet the other partitioning rules defined for a table.
Defining a DEFAULT partition
A DEFAULT partition captures any rows that don't fit into any other partition in a LIST partitioned or subpartitioned table. If you don't include a DEFAULT rule, any row that doesn't match one of the values in the partitioning constraints causes an error. Each LIST partition or subpartition can have its own DEFAULT rule.
The syntax of a DEFAULT rule is:
Where partition_name specifies the name of the partition or subpartition that stores any rows that don't match the rules specified for other partitions.
You can create a list-partitioned table in which the server decides the partition for storing the data based on the value of the country column. In that case, if you attempt to add a row in which the value of the country column contains a value not listed in the rules, an error is reported:
This example creates such a table but adds a DEFAULT partition. The server stores any rows that don't match a value specified in the partitioning rules for europe, asia, or americas partitions in the others partition.
To test the DEFAULT partition, add a row with a value in the country column that doesn't match one of the countries specified in the partitioning constraints:
Query the contents of the sales table to confirm that the previously rejected row is now stored in the sales_others partition:
EDB Postgres Advanced Server provides the following methods to reassign the contents of a DEFAULT partition or subpartition:
You can use the ALTER TABLE… ADD PARTITION command to add a partition to a table with a DEFAULT rule. There can't be conflicting values between existing rows in the table and the values of the partition you're adding. You can alternatively use the ALTER TABLE… SPLIT PARTITION command to split an existing partition.
You can use the ALTER TABLE… ADD SUBPARTITION command to add a subpartition to a table with a DEFAULT rule. There can't be conflicting values between existing rows in the table and the values of the subpartition you're adding. You can alternatively use the ALTER TABLE… SPLIT SUBPARTITION command to split an existing subpartition.
Example: Adding a partition to a table with a DEFAULT partition
This example uses the ALTER TABLE... ADD PARTITION command. It assumes there's no conflicting values between the existing rows in the table and the values of the partition to add.
When the following rows are inserted into the table, an error occurs, indicating that there are conflicting values:
Row (4,'SOUTH AFRICA') conflicts with the VALUES list in the ALTER TABLE... ADD PARTITION statement, thus resulting in an error:
Example: Splitting a DEFAULT partition
This example splits a DEFAULT partition, redistributing the partition's content between two new partitions in the table sales.
This command inserts rows into the table, including rows into the DEFAULT partition:
The partitions include the DEFAULT others partition:
This command shows the rows distributed among the partitions:
This command splits the DEFAULT others partition into partitions named africa and others:
The partitions now include the africa partition along with the DEFAULT others partition:
This command shows that the rows were redistributed across the new partitions:
Defining a MAXVALUE partition
A MAXVALUE partition or subpartition captures any rows that don't fit into any other partition in a range-partitioned or subpartitioned table. If you don't include a MAXVALUE rule, any row that exceeds the maximum limit specified by the partitioning rules causes in an error. Each partition or subpartition can have its own MAXVALUE partition.
The syntax of a MAXVALUE rule is:
Where partition_name specifies the name of the partition that stores any rows that don't match the rules specified for other partitions.
The last example created a range-partitioned table in which the data was partitioned based on the value of the date column. If you attempt to add a row with a date value that exceeds a date listed in the partitioning constraints, EDB Postgres Advanced Server reports an error.
This CREATE TABLE command creates the same table but with a MAXVALUE partition. Instead of throwing an error, the server stores any rows that don't match the previous partitioning constraints in the others partition.
To test the MAXVALUE partition, add a row with a value in the date column that exceeds the last date value listed in a partitioning rule. The server stores the row in the others partition.
Query the contents of the sales table to confirm that the previously rejected row is now stored in the sales_others partition:
EDB Postgres Advanced Server doesn't have a way to reassign the contents of a MAXVALUE partition or subpartition.
You can't use the ALTER TABLE… ADD PARTITION statement to add a partition to a table with a MAXVALUE rule. However, you can use the ALTER TABLE… SPLIT PARTITION statement to split an existing partition.
You can't use the ALTER TABLE… ADD SUBPARTITION statement to add a subpartition to a table with a MAXVALUE rule. However, you can split an existing subpartition with the ALTER TABLE… SPLIT SUBPARTITION statement.