CREATE TABLE...PARTITION BY v13

Use the PARTITION BY clause of the CREATE TABLE command to create a partitioned table with data distributed amongst one or more partitions (and subpartitions). The command syntax comes in the following forms:

List Partitioning Syntax

Use the first 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> ]...)]
   (<list_partition_definition>[, <list_partition_definition>]...)
   [ENABLE ROW MOVEMENT];

Where list_partition_definition is:

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

Range Partitioning Syntax

Use the second 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> ]...)]
   (<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>, ...)]

Hash Partitioning Syntax

Use the third 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> ]...)]
   [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>, ...)]

Subpartitioning Syntax

subpartition may 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 may have one or more subpartitions. There is no upper limit to the number of defined partitions, but if you include the PARTITION BY clause, you must specify at least one partitioning rule. The resulting table will be owned by the user that creates it.

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, but there is no upper limit placed on the number of values you may specify. Include a rule that specifies a matching value of DEFAULT to direct any un-qualified rows to the given partition; for more information about using the DEFAULT keyword, see Handling Stray Values in a LIST or RANGE Partitioned Table.

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

For more information about AUTOMATIC LIST PARTITIONING, see Automatic List Partitioning.

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 (i.e., 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; a date boundary of January 1, 2013 will include only those date values that fall on or before December 31, 2012.

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

For more information about using the MAXVALUE keyword, see Handling Stray Values in a LIST or RANGE Partitioned Table.

Use the INTERVAL clause to specify an interval range partitioned table. By specifying an INTERVAL clause, the range partitioning is extended by the database automatically to create partitions of a specified interval when new data is inserted into a table that exceeds an existing range partition.

For more information about INTERVAL RANGE PARTITION, see Interval Range Partitioning.

Use the PARTITION BY HASH clause to create a hash-partitioned table. In a HASH partitioned table, data is divided amongst 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 is as close to unique as possible to help ensure that data is evenly distributed amongst the partitions. When selecting a partitioning column (or combination of columns), select a column (or columns) that you frequently search for exact matches for best performance.

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

Use the TABLESPACE keyword to specify the name of a tablespace on which a partition or subpartition will reside; if you do not specify a tablespace, the partition or subpartition will reside in the default tablespace.

If a table definition includes the SUBPARTITION BY clause, each partition within that table will have at least one subpartition. Each subpartition may be explicitly defined or system-defined.

If the subpartition is system-defined, the server-generated subpartition will reside in the default tablespace, and the name of the subpartition 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.

The server will generate a subpartition name that is 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.

Use the ENABLE ROW MOVEMENT with a list, range, or hash partition tables to support the migration of tables with similar syntax from oracle databases, however it doesnot enable the actual row movement. Also, this syntax is not supported with ALTER TABLE command.

Parameters

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, available at:

https://www.postgresql.org/docs/current/static/sql-createtable.html

partition_name

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

num

The PARTITIONS num clause can be used to specify the number of HASH partitions to be created. Alternatively, you can use the partition definition to specify individual partitions and their tablespaces.

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

The SUBPARTITIONS num clause is only supported for HASH partitions, and can be used to specify the number of hash subpartitions to be created. Alternatively, you can use the subpartition definition to specify individual subpartitions and their tablespaces. If no SUBPARTITIONS or SUBPARTITION DEFINITION is specified, then the partition creates a subpartition based on the SUBPARTITION TEMPLATE.

column

The name of a 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(s).

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 will be grouped into partitions. Each partitioning rule must specify at least one value, but there is no limit placed on the number of values specified within a rule. value may 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 un-matched rows to the given partition. If you do not include a rule that includes 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 specifying rules for a list-partitioned table, include the MAXVALUE keyword in the last partition rule to direct any un-categorized rows to the given partition. If you do not include a MAXVALUE partition, any INSERT statement that attempts to add a row where the partitioning 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.