A DEFAULT or MAXVALUE partition or subpartition captures any rows that do not meet the other partitioning rules defined for a table.

Define a DEFAULT partition

A DEFAULT partition captures any rows that do not fit into any other partition in a LIST partitioned (or subpartitioned) table. If you do not include a DEFAULT rule, any row that does not match one of the values in the partitioning constraints will cause an error. Each LIST partition or subpartition may have its own DEFAULT rule.

The syntax of a DEFAULT rule is as follows:
PARTITION partition_name VALUES (DEFAULT)

Where partition_name specifies the name of the partition or subpartition used to store any rows that do not match the partitioning rules specified for other partitions.

In the last example, a list-partitioned table is created. The server determines which partition in this partitioned table to store the data based on the value of the country column. If you attempt to add a row in which the value of the country column is not listed in the partitioning rules, PolarDB-O reports an error:
acctg=# INSERT INTO sales VALUES
acctg-#   (40, '3000x', 'IRELAND', '01-Mar-2012', '45000');
ERROR:  inserted partition key does not map to any partition
The following example creates the same table, but adds a DEFAULT partition. The server will store any rows that do not match a value specified in the partitioning rules for the europe, asia, or americas partition in the others partition.
CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA'),
  PARTITION others VALUES (DEFAULT)
);
To test the DEFAULT partition, add a row with a value in the country column that does not match any country specified in the partitioning constraints:
INSERT INTO sales VALUES
  (40, '3000x', 'IRELAND', '01-Mar-2012', '45000');
Querying the sales table confirms that the previously rejected row is now stored in the sales_others partition:
acctg=# SELECT tableoid::regclass, * FROM sales;
    tableoid    | dept_no | part_no | country  |        date        |  amount 
----------------+---------+---------+----------+--------------------+--------
 sales_europe   |      10 | 4519b   | FRANCE   | 17-JAN-12 00:00:00 |   45000
 sales_europe   |      10 | 9519b   | ITALY    | 07-JUL-12 00:00:00 |   15000
 sales_europe   |      10 | 9519a   | FRANCE   | 18-AUG-12 00:00:00 |  650000
 sales_europe   |      10 | 9519b   | FRANCE   | 18-AUG-12 00:00:00 |  650000
 sales_asia     |      20 | 3788a   | INDIA    | 01-MAR-12 00:00:00 |   75000
 sales_asia     |      20 | 3788a   | PAKISTAN | 04-JUN-12 00:00:00 |   37500
 sales_asia     |      20 | 3788b   | INDIA    | 21-SEP-12 00:00:00 |    5090
 sales_asia     |      20 | 4519a   | INDIA    | 18-OCT-12 00:00:00 |  650000
 sales_asia     |      20 | 4519b   | INDIA    | 02-DEC-12 00:00:00 |    5090
 sales_americas |      40 | 9519b   | US       | 12-APR-12 00:00:00 |  145000
 sales_americas |      40 | 4577b   | US       | 11-NOV-12 00:00:00 |   25000
 sales_americas |      30 | 7588b   | CANADA   | 14-DEC-12 00:00:00 |   50000
 sales_americas |      30 | 9519b   | CANADA   | 01-FEB-12 00:00:00 |   75000
 sales_americas |      30 | 4519b   | CANADA   | 08-APR-12 00:00:00 |  120000
 sales_americas |      40 | 3788a   | US       | 12-MAY-12 00:00:00 |    4950
 sales_americas |      40 | 4788a   | US       | 23-SEP-12 00:00:00 |    4950
 sales_americas |      40 | 4788b   | US       | 09-OCT-12 00:00:00 |   15000
 sales_others   |      40 | 3000x   | IRELAND  | 01-MAR-12 00:00:00 |   45000
(18 rows)
Note that PolarDB-O does not include a method to reassign the content of a DEFAULT partition or subpartition.
  • You cannot use the ALTER TABLE... ADD PARTITION command to add a partition to a table with a DEFAULT rule. However, you can use the ALTER TABLE... SPLIT PARTITION command to split an existing partition.
  • You cannot use the ALTER TABLE... ADD SUBPARTITION command to add a subpartition to a table with a DEFAULT rule. However, you can use the ALTER TABLE... SPLIT SUBPARTITION command to split an existing subpartition.

Define a MAXVALUE partition

A MAXVALUE partition or subpartition captures any rows that do not fit into any other partition in a range-partitioned or subpartitioned table. If you do not include a MAXVALUE rule, any row that exceeds the maximum limit specified by the partitioning rules will cause an error. Each partition or subpartition may have its own MAXVALUE partition.

Note that PolarDB-O does not include a method to reassign the content of a MAXVALUE partition or subpartition:
  • You cannot use the ALTER TABLE... ADD PARTITION command to add a partition to a table with a MAXVALUE rule. However, you can use the ALTER TABLE... SPLIT PARTITION command to split an existing partition.
  • You cannot use the ALTER TABLE... ADD SUBPARTITION command to add a subpartition to a table with a MAXVALUE rule. However, you can use the ALTER TABLE... SPLIT SUBPARTITION command to split an existing subpartition.
The syntax of a MAXVALUE rule is as follows:
PARTITION partition_name VALUES LESS THAN (MAXVALUE)

Where partition_name specifies the name of the partition used to store any rows that do not match the partitioning rules specified for other partitions.

In the last example, a range-partitioned table is created. The data in this table is partitioned based on the value of date column. If you attempt to add a row in which the value of the date column exceeds a date listed in the partitioning constraints, PolarDB-O reports an error:
acctg=# INSERT INTO sales VALUES
acctg-#   (40, '3000x', 'IRELAND', '01-Mar-2013', '45000');
ERROR:  inserted partition key does not map to any partition
The following CREATE TABLE command creates the same table, but this table has a MAXVALUE partition. Instead of reporting an error, the server will store any rows that do not match the previous partitioning constraints in the others partition:
CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01'),
  PARTITION others VALUES LESS THAN (MAXVALUE)
);
To test the MAXVALUE partition, add a row with a value in the date column that exceeds the last date value listed in each partitioning rule. The server will store this row in the others partition:
INSERT INTO sales VALUES
                    (40, '3000x', 'IRELAND', '2015-Oct-01', '45000');
Querying the sales table confirms that the previously rejected row is now stored in the sales_others partition:
acctg=# SELECT tableoid::regclass, * FROM sales;  
   tableoid    | dept_no | part_no | country  |        date        | amount 
---------------+---------+---------+----------+--------------------+---------
 sales_q1_2012 |      10 | 4519b   | FRANCE   | 17-JAN-12 00:00:00 |    45000
 sales_q1_2012 |      20 | 3788a   | INDIA    | 01-MAR-12 00:00:00 |    75000
 sales_q1_2012 |      30 | 9519b   | CANADA   | 01-FEB-12 00:00:00 |    75000
 sales_q2_2012 |      40 | 9519b   | US       | 12-APR-12 00:00:00 |   145000
 sales_q2_2012 |      20 | 3788a   | PAKISTAN | 04-JUN-12 00:00:00 |    37500
 sales_q2_2012 |      30 | 4519b   | CANADA   | 08-APR-12 00:00:00 |   120000
 sales_q2_2012 |      40 | 3788a   | US       | 12-MAY-12 00:00:00 |     4950
 sales_q3_2012 |      10 | 9519b   | ITALY    | 07-JUL-12 00:00:00 |    15000
 sales_q3_2012 |      10 | 9519a   | FRANCE   | 18-AUG-12 00:00:00 |   650000
 sales_q3_2012 |      10 | 9519b   | FRANCE   | 18-AUG-12 00:00:00 |   650000
 sales_q3_2012 |      20 | 3788b   | INDIA    | 21-SEP-12 00:00:00 |     5090
 sales_q3_2012 |      40 | 4788a   | US       | 23-SEP-12 00:00:00 |     4950
 sales_q4_2012 |      40 | 4577b   | US       | 11-NOV-12 00:00:00 |    25000
 sales_q4_2012 |      30 | 7588b   | CANADA   | 14-DEC-12 00:00:00 |    50000
 sales_q4_2012 |      40 | 4788b   | US       | 09-OCT-12 00:00:00 |    15000
 sales_q4_2012 |      20 | 4519a   | INDIA    | 18-OCT-12 00:00:00 |   650000
 sales_q4_2012 |      20 | 4519b   | INDIA    | 02-DEC-12 00:00:00 |     5090
 sales_others  |      40 | 3000x   | IRELAND  | 01-MAR-13 00:00:00 |    45000
(18 rows)