Handling stray values in a LIST or RANGE partitioned table v14
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:
PARTITION [<partition_name>] VALUES (DEFAULT)
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:
edb=# INSERT INTO sales VALUES edb-# (40, '3000x', 'IRELAND', '01-Mar-2012', '45000'); ERROR: no partition of relation "sales_2012" found for row DETAIL: Partition key of the failing row contains (country) = (IRELAND).
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.
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 doesn't match one of the countries specified in the partitioning constraints:
INSERT INTO sales VALUES (40, '3000x', 'IRELAND', '01-Mar-2012', '45000');
Query the contents of the sales
table to confirm that the previously rejected row is now stored in the sales_others
partition:
edb=# SELECT tableoid::regclass, * FROM sales; tableoid | dept_no | part_no | country | date | amount ----------------+---------+---------+----------+--------------------+-------- 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_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_others | 40 | 3000x | IRELAND | 01-MAR-12 00:00:00 | 45000 (18 rows)
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 aDEFAULT
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 theALTER 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 aDEFAULT
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 theALTER 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.
edb=# ALTER TABLE sales ADD PARTITION africa values ('SOUTH AFRICA', 'KENYA'); ALTER TABLE
When the following rows are inserted into the table, an error occurs, indicating that there are conflicting values:
edb=# INSERT INTO sales (dept_no, country) VALUES (1,'FRANCE'),(2,'INDIA'),(3,'US'),(4,'SOUTH AFRICA'),(5,'NEPAL'); INSERT 0 5
Row (4,'SOUTH AFRICA')
conflicts with the VALUES
list in the ALTER TABLE... ADD PARTITION
statement, thus resulting in an error:
edb=# ALTER TABLE sales ADD PARTITION africa values ('SOUTH AFRICA', 'KENYA'); ERROR: updated partition constraint for default partition "sales_others" would be violated by some row
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:
INSERT INTO sales VALUES (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'), (10, '9519b', 'ITALY', '07-Jul-2012', '15000'), (20, '3788a', 'INDIA', '01-Mar-2012', '75000'), (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'), (30, '9519b', 'US', '12-Apr-2012', '145000'), (30, '7588b', 'CANADA', '14-Dec-2012', '50000'), (40, '4519b', 'SOUTH AFRICA', '08-Apr-2012', '120000'), (40, '4519b', 'KENYA', '08-Apr-2012', '120000'), (50, '3788a', 'CHINA', '12-May-2012', '4950');
The partitions include the DEFAULT others
partition:
edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS; partition_name | high_value ----------------+--------------------- EUROPE | 'FRANCE', 'ITALY' ASIA | 'INDIA', 'PAKISTAN' AMERICAS | 'US', 'CANADA' OTHERS | DEFAULT (4 rows)
This command shows the rows distributed among the partitions:
edb=# SELECT tableoid::regclass, * FROM sales; tableoid | dept_no| part_no | country | date | amount --------------+--------+---------+--------------+--------------------+------- sales_americas| 30 | 9519b | US | 12-APR-12 00:00:00 |145000 sales_americas| 30 | 7588b | CANADA | 14-DEC-12 00:00:00 | 50000 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_asia | 20 | 3788a | INDIA | 01-MAR-12 00:00:00 | 75000 sales_asia | 20 | 3788a | PAKISTAN | 04-JUN-12 00:00:00 | 37500 sales_others | 40 | 4519b | SOUTH AFRICA | 08-APR-12 00:00:00 |120000 sales_others | 40 | 4519b | KENYA | 08-APR-12 00:00:00 |120000 sales_others | 50 | 3788a | CHINA | 12-MAY-12 00:00:00 | 4950 (9 rows)
This command splits the DEFAULT others
partition into partitions named africa
and others
:
ALTER TABLE sales SPLIT PARTITION others VALUES ('SOUTH AFRICA', 'KENYA') INTO (PARTITION africa, PARTITION others);
The partitions now include the africa
partition along with the DEFAULT others
partition:
edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS; partition_name | high_value ----------------+------------------------- EUROPE | 'FRANCE', 'ITALY' ASIA | 'INDIA', 'PAKISTAN' AMERICAS | 'US', 'CANADA' AFRICA | 'SOUTH AFRICA', 'KENYA' OTHERS | DEFAULT (5 rows)
This command shows that the rows were redistributed across the new partitions:
edb=# SELECT tableoid::regclass, * FROM sales; tableoid |dept_no | part_no | country | date | amount ---------------+--------+---------+-------------+--------------------+------- sales_americas | 30 | 9519b | US | 12-APR-12 00:00:00 |145000 sales_americas | 30 | 7588b | CANADA | 14-DEC-12 00:00:00 | 50000 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_asia | 20 | 3788a | INDIA | 01-MAR-12 00:00:00 | 75000 sales_asia | 20 | 3788a | PAKISTAN | 04-JUN-12 00:00:00 | 37500 sales_africa | 40 | 4519b | SOUTH AFRICA| 08-APR-12 00:00:00 |120000 sales_africa | 40 | 4519b | KENYA | 08-APR-12 00:00:00 |120000 sales_others_1 | 50 | 3788a | CHINA | 12-MAY-12 00:00:00 | 4950 (9 rows)
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:
PARTITION [<partition_name>] VALUES LESS THAN (MAXVALUE)
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.
edb=# INSERT INTO sales VALUES edb-# (40, '3000x', 'IRELAND', '01-Mar-2013', '45000'); ERROR: no partition of relation "sales" found for row DETAIL: Partition key of the failing row contains (date) = (01-MAR-13 00:00:00).
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.
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 a partitioning rule. The server stores the row in the others
partition.
INSERT INTO sales VALUES (40, '3000x', 'IRELAND', '01-Mar-2013', '45000');
Query the contents of the sales
table to confirm that the previously rejected row is now stored in the sales_others
partition:
edb=# 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)
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 aMAXVALUE
rule. However, you can use theALTER 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 aMAXVALUE
rule. However, you can split an existing subpartition with theALTER TABLE… SPLIT SUBPARTITION
statement.