You can use the PARTITION BY clause of the CREATE TABLE command to create a partitioned table. Data in this partitioned table is distributed among one or more partitions (and subpartitions).

Overview

The CREATE TABLE command syntax has the following four forms:

  • List partitioning syntax
    The first form is to create a list-partitioned table:
    CREATE TABLE [ schema. ]table_name table_definition PARTITION BY 
        LIST(column)
        [SUBPARTITION BY {RANGE|LIST} (column[, column ]...)] 
        (list_partition_definition[, list_partition_definition]...) ;
    Where list_partition_definition is:
    PARTITION [partition_name] 
    VALUES (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]
  • Range partitioning syntax
    The second form is to create a range-partitioned table:
    CREATE TABLE [ schema. ]table_name 
       table_definition
       PARTITION BY RANGE(column[, column ]...)
       [SUBPARTITION BY {RANGE|LIST} (column[, column ]...)]
       (range_partition_definition[, range_partition_definition]...) ;
    Where range_partition_definition is:
    PARTITION [partition_name]
      VALUES LESS THAN (value[, value]...)
      [TABLESPACE tablespace_name]
      [(subpartition, ...)]
  • Subpartitioning syntax
    subpartition may be one of the following two types:
    {list_subpartition | range_subpartition}
    Where list_subpartition is:
    SUBPARTITION [subpartition_name] VALUES (value[, value]...)
    [TABLESPACE tablespace_name]
    Where range_subpartition is:
    SUBPARTITION [subpartition_name]
    VALUES LESS THAN (value[, value]...) 
    [TABLESPACE tablespace_name] 

Description

The CREATE TABLE... PARTITION BY command creates a table that has one or multiple partitions. Each partition may have one or multiple subpartitions. The number of defined partitions is not limited. If you include the PARTITION BY clause, you must specify a minimum of one partitioning rule. The resulting table is owned by the user who creates the table.

Use the PARTITION BY LIST clause to divide a table into partitions based on the values entered in a specified column. Each partitioning rule must specify a minimum of one literal value. The number of values you may specify is not limited. Include a rule that specifies a matching value of DEFAULT to direct any un-qualified rows to the specified partition.

Use the PARTITION BY RANGE clause to specify boundary rules based on which partitions are created. Each partitioning rule must contain at least one column of a data type that has two operators (for example, a greater-than or equal to operator, and a less-than operator). Range boundaries are evaluated based on a LESS THAN clause and are non-inclusive. A date boundary of January 1, 2013 only includes the date values that fall on or before December 31, 2012.

Range partitioning rules must be specified in ascending order. If INSERT commands store rows with values that exceed the top boundary of a range-partitioned table, the commands will fail. However, commands will not fail if the partitioning rules include a boundary rule that specifies a value of MAXVALUE. If you do not include a MAXVALUE rule, any row that exceeds the maximum limit specified by the boundary rules will cause an error.

Use the TABLESPACE keyword to specify the name of a tablespace in which a partition or subpartition will reside. If you do not specify a tablespace, the partition or subpartition will be created in the default tablespace.

If you use the CREATE TABLE syntax to create an index on a partitioned table, the index will be created on each partition or subpartition.

If the table definition includes the SUBPARTITION BY clause, each partition in the table will have a minimum of one subpartition. Each subpartition can be explicitly defined or system-defined.

If the subpartition is system-defined, the server-generated subpartition will reside in the default tablespace, and the subpartition name will be assigned by the server. The server will create:
  • A DEFAULT subpartition if the SUBPARTITION BY clause specifies LIST.
  • A MAXVALUE subpartition if the SUBPARTITION BY clause specifies RANGE.

A subpartition name generated by the server is a combination of the partition name and a unique identifier. You can query the ALL_TAB_SUBPARTITIONS table to view a complete list of subpartition names.

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the table to be created.
table_definition The column names, data types, and constraint information as described in the PostgreSQL core documentation for the CREATE TABLE statement is available at CREATE TABLE.
partition_name The name of the partition to be created. Partition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
subpartition_name The name of the subpartition to be created. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
column The name of the column on which the partitioning rules are based. Each row will be stored in a partition that corresponds to the value of the specified column.
(value[, value]...)

Use value to specify a quoted literal value (or a list of literal values separated by commas) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but the number of values specified in a rule is not limited. value may be null, default (if specifying a LIST partition), or maxvalue (if specifying a RANGE partition).

When you specify rules for a list-partitioned table, include the DEFAULT keyword in the last partition rule to direct any unmatched rows to the specified partition. If you do not include a value of DEFAULT, any INSERT statement that attempts to add a row that does not match the specified rules of at least one partition will fail and return an error.

When you specify rules for a range-partitioned table, include the MAXVALUE keyword in the last partition rule to direct any un-categorized rows to the specified partition. If you do not include a MAXVALUE partition, any INSERT statement that attempts to add a row where the partition key is greater than the highest value specified will fail and return an error.

tablespace_name The name of the tablespace in which the partition or subpartition resides.

Example - PARTITION BY LIST

The following example uses the PARTITION BY LIST clause to create a partitioned table named sales. The sales table stores information in three partitions (europe, asia, and americas):
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')
);
The resulting table is partitioned based on the value specified in the country column:
acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(3 rows)
  • Rows with a value of US or CANADA in the country column are stored in the americas partition.
  • Rows with a value of INDIA or PAKISTAN in the country column are stored in the asia partition.
  • Rows with a value of FRANCE or ITALY in the country column are stored in the europe partition.
The server evaluates the following statement based on the partitioning rules and stores the row in the europe partition:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');

Example - PARTITION BY RANGE

The following example uses the PARTITION BY RANGE clause to create a partitioned table named sales. The sales table stores information in four partitions (q1_2012, q2_2012, q3_2012, and q4_2012).
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')
);
The resulting table is partitioned based on the value specified in the date column:
acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q4_2012        | '2013-Jan-01' 
 q3_2012        | '2012-Oct-01' 
 q2_2012        | '2012-Jul-01' 
 q1_2012        | '2012-Apr-01' 
(4 rows)
  • Rows with a value in the date column before April 1, 2012 are stored in the q1_2012 partition.
  • Rows with a value in the date column before July 1, 2012 are stored in the q2_2012 partition.
  • Rows with a value in the date column before October 1, 2012 are stored in the q3_2012 partition.
  • Rows with a value in the date column before January 1, 2013 are stored in the q4_2012 partition.
The server evaluates the following statement based on the partitioning rules and stores the row in the q3_2012 partition:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');

Example - PARTITION BY RANGE, SUBPARTITION BY LIST

The following example creates a partitioned table (sales) that is first partitioned by using the transaction date. Then, the range partitions (q1_2012, q2_2012, q3_2012, and q4_2012) are list-partitioned by using the value of the country column.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST(country)
  (
    PARTITION q1_2012 
      VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
       ),
  PARTITION q2_2012 
    VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
       ),
  PARTITION q3_2012 
    VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
       ),
  PARTITION q4_2012 
    VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
       )
);
The table created by using this statement has four partitions. Each partition has three subpartitions:
acctg=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value | partition_name     +    +    
q4_asia    | 'INDIA', 'PAKISTAN' | q4_2012
q4_europe    | 'FRANCE', 'ITALY' | q4_2012
SUBPARTITION q4_ SUBPARTITION q4_ SUBPARTITION q4_
q4_americas    | 'US', 'CANADA'    | q4_2012 
q3_americas    | 'US', 'CANADA'    | q3_2012 
q3_asia        | 'INDIA', 'PAKISTAN'     | q3_2012 
q3_europe    | 'FRANCE', 'ITALY'    | q3_2012
q2_americas    | 'US', 'CANADA'    | q2_2012
q2_asia        | 'INDIA','PAKISTAN'     | q2_2012
q2_europe    | 'FRANCE', 'ITALY'    | q2_2012
q1_americas    | 'US', 'CANADA'     | q1_2012
q1_asia        | 'INDIA', 'PAKISTAN'     | q1_2012
q1_europe    | 'FRANCE', 'ITALY'    | q1_2012
(12 rows)

When a row is added to this table, the value in the date column is compared with the values specified in the range partitioning rules. The server selects the partition in which the row will reside. The value in the country column is then compared with the values specified in the list subpartitioning rules. When the server locates a match for the value, the row is stored in the corresponding subpartition.

Any row added to the table is stored in a subpartition. Therefore, all partitions contain no data.

The server evaluates the following statement based on the partitioning and subpartitioning rules and stores the row in the q3_europe partition:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');