Defining a DEFAULT partition v17

Suggest edits

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.

Adding a DEFAULT partition

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)
);

Testing the DEFAULT 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:

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;
Output
    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 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.

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;
Output
 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;
Output
 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;
Output
 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;
Output
 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)
← Prev

Handling stray values in a LIST or RANGE partitioned table

↑ Up

Handling stray values in a LIST or RANGE partitioned table

Next →

Defining a MAXVALUE partition