CREATE TABLE...PARTITION BY v16

Use the PARTITION BY clause of the CREATE TABLE command to create a partitioned table with data distributed among one or more partitions and subpartitions. The syntax can take several forms.

List partitioning syntax

Use this form to create a list-partitioned table:

CREATE TABLE [ schema. ]<table_name> 
   <table_definition>
   PARTITION BY LIST(<column>) [ AUTOMATIC ]
   [SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...) 
   [SUBPARTITION TEMPLATE (<subpartition_template_description>)]]
   (<list_partition_definition>[, <list_partition_definition>]...)
   [ENABLE ROW MOVEMENT];

Where `list_partition_definition` is:

      PARTITION [<partition_name>]
        VALUES (<value>[, <value>]...)
        [TABLESPACE <tablespace_name>]
        [(<subpartition>, ...)]

Where `subpartition_template_description` is:

      SUBPARTITION [<subpartition_name>] 
        VALUES (<value>[, <value>]...) 
        [TABLESPACE <tablespace_name>], ...

Range partitioning syntax

Use this form to create a range-partitioned table:

CREATE TABLE [ schema. ]<table_name>
   <table_definition>
   PARTITION BY RANGE(<column>[, <column> ]...)
   [INTERVAL (<constant> | <expression>)]
   [SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...) 
   [SUBPARTITION TEMPLATE (<subpartition_template_description>)]]
   (<range_partition_definition>[, <range_partition_definition>]...)
   [ENABLE ROW MOVEMENT];

Where `range_partition_definition` is:

      PARTITION [<partition_name>]
        VALUES LESS THAN (<value>[, <value>]...)
        [TABLESPACE <tablespace_name>]
        [(<subpartition>, ...)]

Where `subpartition_template_description` is:

      SUBPARTITION [<subpartition_name>] 
        VALUES LESS THAN (<value>[, <value>]...)
        [TABLESPACE <tablespace_name>], ...

Hash partitioning syntax

Use this form to create a hash-partitioned table:

CREATE TABLE [ schema. ]<table_name>
   <table_definition>
   PARTITION BY HASH(<column>[, <column> ]...)
   [SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...) 
   [SUBPARTITION TEMPLATE (<subpartition_template_description>)]]
   [SUBPARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]]
   [PARTITIONS <num>] [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ] |
   (<hash_partition_definition>[, <hash_partition_definition>]...)
   [ENABLE ROW MOVEMENT];

Where `hash_partition_definition` is:

      PARTITION [<partition_name>]
        [TABLESPACE <tablespace_name>]
        [(<subpartition>, ...)]

Where `subpartition_template_description` is:

      SUBPARTITION [<subpartition_name>] 
        [TABLESPACE <tablespace_name>], ...

Subpartitioning syntax

subpartition can be one of the following:

{<list_subpartition> | <range_subpartition> | <hash_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>]

Where `hash_subpartition` is:

      SUBPARTITION [<subpartition_name>]
        [TABLESPACE <tablespace_name>] |
      SUBPARTITIONS <num> [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]

Description

The CREATE TABLE… PARTITION BY command creates a table with one or more partitions. Each partition can have one or more subpartitions. There's no upper limit to the number of defined partitions. However, if you include the PARTITION BY clause, you must specify at least one partitioning rule. The resulting table is owned by the user that creates it.

PARTITION BY LIST clause

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 at least one literal value. However, there's no upper limit placed on the number of values you can specify. Include a rule that specifies a matching value of DEFAULT to direct any unqualified rows to the given partition.

For more information about using the DEFAULT keyword, see Handling stray values in a LIST or RANGE partitioned table.

AUTOMATIC clause

Use the AUTOMATIC clause to specify for the table to use automatic list partitioning. By specifying the AUTOMATIC clause, the database creates partitions when new data is inserted into a table that doesn't correspond to any value declared for an existing partition.

For more information about AUTOMATIC LIST PARTITIONING, see Automatic list partitioning.

PARTITION BY RANGE clause

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

Specify range partition rules in ascending order. INSERT commands that store rows with values that exceed the top boundary of a range-partitioned table fail unless the partitioning rules include a boundary rule that specifies a value of MAXVALUE. If you don't include a MAXVALUE partitioning rule, any row that exceeds the maximum limit specified by the boundary rules results in an error.

For more information about using the MAXVALUE keyword, see Handling stray values in a LIST or RANGE partitioned table.

INTERVAL clause

Use the INTERVAL clause to specify an interval range-partitioned table. With an INTERVAL clause, the database extends the range partitioning to create partitions of a specified interval when data is inserted into a table that exceeds an existing range partition.

For more information about INTERVAL RANGE PARTITION, see Interval range partitioning.

PARTITION BY HASH clause

Use the PARTITION BY HASH clause to create a hash-partitioned table. In a HASH partitioned table, data is divided among equal-sized partitions based on the hash value of the column specified in the partitioning syntax. When specifying a HASH partition, choose a column or combination of columns that's as close to unique as possible. This technique helps to ensure that data is evenly distributed among the partitions. When selecting a partitioning column (or combination of columns), for best performance, select columns that you frequently search for exact matches.

STORE IN clause

Use the STORE IN clause to specify the tablespace list across which the autogenerated partitions or subpartitions are stored.

TABLESPACE keyword

Use the TABLESPACE keyword to specify the name of a tablespace on which a partition or subpartition resides. if you don't specify a tablespace, the partition or subpartition resides in the default tablespace.

SUBPARTITION BY clause

If a table definition includes the SUBPARTITION BY clause, each partition in that table must have at least one subpartition. You can explicitly define each subpartition, or it can be system defined.

If the subpartition is system defined, the server-generated subpartition resides in the default tablespace, and the name of the subpartition is assigned by the server. The server creates:

• A DEFAULT subpartition if the SUBPARTITION BY clause specifies LIST.
• A MAXVALUE subpartition if the SUBPARTITION BY clause specifies RANGE.

The server generates a subpartition name that's a combination of the partition table name and a unique identifier. You can query the ALL_TAB_SUBPARTITIONS table to review a complete list of subpartition names.

SUBPARTITION TEMPLATE

Use ENABLE ROW MOVEMENT with a list, range, or hash partition table to support migrating tables with similar syntax from Oracle databases. However, it doesn't enable the actual row movement. This syntax isn't supported with the ALTER TABLE command.

Parameters

table_name

The name (optionally schema-qualified) of the table to create.

table_definition

The column names, data types, and constraint information as described in the PostgreSQL core documentation for the CREATE TABLE statement.

partition_name

The name of the partition to create. 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 create. Subpartition names must be unique among all partitions and subpartitions and must follow the naming conventions for object identifiers.

num

You can use the PARTITIONS num clause to specify the number of HASH partitions to create. Alternatively, you can use the partition definition to specify individual partitions and their tablespaces.

!!! Note You can use either PARTITIONS or PARTITION DEFINITION when creating a table.

The SUBPARTITIONS num clause is supported only for HASH partitions. You can use it to specify the number of hash subpartitions to create. Alternatively, you can use the subpartition definition to specify individual subpartitions and their tablespaces. If you don't specify SUBPARTITIONS or SUBPARTITION DEFINITION, then the partition creates a subpartition based on the SUBPARTITION TEMPLATE.

subpartition_template_description

The list of subpartition details with subpartition name, subpartition bound, and tablespace name.

column

The name of a column on which the partitioning rules are based. Each row is stored in a partition that corresponds to the value of the specified columns.

constant | expression

The constant and expression specifies a NUMERIC, DATE, or TIME value.

(value[, value]...)

Use value to specify a quoted literal value or comma-delimited list of literal values by which table entries are grouped into partitions. Each partitioning rule must specify at least one value. However, there's no limit on the number of values specified in a rule. value can be NULL, DEFAULT if specifying a LIST partition, or MAXVALUE if specifying a RANGE partition.

When specifying rules for a list-partitioned table, include the DEFAULT keyword in the last partition rule to direct any unmatched rows to the given partition. If you don't include a rule that includes a value of DEFAULT, any INSERT statement that attempts to add a row that doesn't match the specified rules of at least one partition fails and returns an error.

When specifying rules for a list-partitioned table, include the MAXVALUE keyword in the last partition rule to direct any uncategorized rows to the given partition. If you don't include a MAXVALUE partition, any INSERT statement that attempts to add a row where the partitioning key is greater than the highest value specified fails and returns an error.

tablespace_name

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

• On this page
• List partitioning syntax
• Range partitioning syntax
• Hash partitioning syntax
• Subpartitioning syntax
• Description
• Parameters