Partitioning Options ​

This section describes the PARTITION BY syntax in detail.

range_partition_spec ​

RANGE ( column BETWEEN start AND end EACH interval [, OUTSIDE RANGE] [, IS NULL] )

RANGE

Define a number of partitions based on ranges of values bounded by an interval.

column

A column that exists in the partitioned table. You can define a maximum of four partition columns per table. You cannot specify multiple partitions on the same column. You cannot use expressions to define partition columns.

BETWEEN start AND end EACH interval

Specify the endpoints of the range, then specify the interval that marks the boundary between partitions. Use literal values with compatible data types. For example:

between date '2017-01-01' and date '2017-06-30' each interval '1 day'

between timestamp '2010-01-01 00:00:00' and timestamp '2016-12-31 23:59:59' each interval '1 year'

between 1 and 10000 each 1

Make sure that the number of partitions derived from the specification does not exceed 32767. For example, the following range is not allowed:

between 1 and 1000000 each 1

The following range is valid:

between 1 and 1000000 each 100

OUTSIDE RANGE

Optionally, create two separate partitions specifically for values that fall outside (above or below) the specified range. One of these partitions will contain values below the range, and one will contain values above the range.

For example, the following range specification creates 13 partitions, including two OUTSIDE RANGE partitions and an IS NULL partition:

range (range_col between 1 and 10 each 1, outside range, is null)

If you query the sys.table_partition view for information about a table that has 13 partitions, you will see results like this:

premdb=# select * from sys.table_partition;
 table_id | n1 | n2 | n3 | n4 | count 
----------+----+----+----+----+-------
    16422 |  0 |  0 |  0 |  0 |    21
    16422 |  1 |  0 |  0 |  0 |   462
    16422 |  2 |  0 |  0 |  0 |   462
    16422 |  3 |  0 |  0 |  0 |   462
    16422 |  4 |  0 |  0 |  0 |   380
    16422 |  5 |  0 |  0 |  0 |   380
    16422 |  6 |  0 |  0 |  0 |   380
    16422 |  7 |  0 |  0 |  0 |   380
    16422 |  8 |  0 |  0 |  0 |   380
    16422 |  9 |  0 |  0 |  0 |   380
    16422 | 10 |  0 |  0 |  0 |   380
    16422 | 11 |  0 |  0 |  0 |   510
    16422 | 12 |  0 |  0 |  0 |     0
(13 rows)

In this result set:

• Row n1=0 counts values that fall below the range
• Rows n1=1 through n1=10 count values in the range
• Row n1=11 counts values that fall above the range
• Row n1=12 counts null values

OUTSIDE RANGE and IS NULL can be specified in any order.

IS NULL

Optionally, create a separate partition that holds any NULL values. OUTSIDE RANGE and IS NULL can be specified in any order.

Note: If the partition column may contain NULL values, be sure to specify IS NULL, or you will be unable to create the table.

hash_partition_spec ​

HASH ( column WITH number PARTITIONS [, IS NULL] )

HASH

Define a number of partitions based on hashing the values in a column.

column

A column that exists in the partitioned table. You can define a maximum of four partitioned columns per table. You cannot specify multiple partitions on the same column.

WITH number PARTITIONS

Enter a number between 2 and 32767. If an IS NULL partition is specified, the minimum number is 1 and the maximum number is 32766. For example:

with 300 partitions

IS NULL

Optionally, create a separate partition that holds any NULL values. IS NULL is optional for hash-partitioned tables, but OUTSIDE RANGE is not meaningful and not required.

Note: If the partition column may contain NULL values, be sure to specify IS NULL, or you will be unable to create the table.

Supported Data Types ​

The following table shows which column data types are supported for range partitioning and hash partitioning.

*You cannot hash partition RANDOMIZED encryption columns.

General Restrictions on Partitioning Tables ​

• Up to four columns in a single table can be partitioned.
• The number of partitions produced by a single specification must be no greater than 32767.
• The product of the number of partitions produced by all specifications for the same table must be no greater than 250000. For example, the following table cannot be created:

premdb=# create table hashtable(a int not null, b int not null) 
partition by (hash(a with 3000 partitions), hash(b with 1000 partitions));
ERROR:  the product of the number of partitions produced by all specifications results in 3000000 partitions, 250000 allowed

• Replicated tables cannot be partitioned.
• Partitioning schemes cannot be altered after a table is created. To change partitioning, you have to re-create the table.

Examples ​

For example, the following table has two range-based partition columns:

• A DATE column, with the partitions separated by an interval of 1 year within a given date range.
• An INTEGER column, using an interval of 1 for the partitions within a given range of numbers. This column also specifies an optional OUTSIDE RANGE partition that captures any values outside of the defined range and an optional IS NULL partition that captures NULL values.

premdb=# create table newmatchstats(seasonid smallint not null, matchday date, htid smallint, atid smallint, moment varchar(5)) 
distribute on (seasonid) 
partition by (
range (matchday between date '8/1/1992' and date '5/31/2017' each interval '1 year', is null),
range (htid between 2 and 51 each 1, outside range, is null)
);
CREATE TABLE
premdb=# \d newmatchstats
       Table "public.newmatchstats"
  Column  |         Type         | Modifiers 
----------+----------------------+-----------
 seasonid | smallint             | not null
 matchday | date                 | 
 htid     | smallint             | 
 atid     | smallint             | 
 moment   | character varying(5) | 

Distribution: Hash (seasonid)
Partition columns: 
   "matchday"	 RANGE (BETWEEN date '1992-08-01' AND date '2017-05-31' EACH interval '1 year')
   "htid"	 RANGE (BETWEEN 2 AND 51 EACH 1, IS NULL, OUTSIDE RANGE)

The following version of the newmatchstats table has 100 hash partitions based on values in the matchday column:

premdb=# create table newmatchstats(seasonid smallint not null, matchday date, htid smallint, atid smallint, moment varchar(5)) 
distribute on (seasonid) 
partition by (hash(matchday with 100 partitions, is null));
CREATE TABLE
premdb=# \d newmatchstats
       Table "public.newmatchstats"
  Column  |         Type         | Modifiers 
----------+----------------------+-----------
 seasonid | smallint             | not null
 matchday | date                 | 
 htid     | smallint             | 
 atid     | smallint             | 
 moment   | character varying(5) | 

Distribution: Hash (seasonid)
Partition Columns: 
   "matchday"	 HASH (WITH 100 PARTITIONS)

premdb=# create table partition_test (c1 int, c2 int) partition by (range (c1 between 1 and 10 each 1, is null, outside range));
CREATE TABLE
premdb=# \d partition_test
Table "public.partition_test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | 
 c2     | integer | 

Distribution: Hash (c1)
Partition Columns: 
   "c1"	 RANGE (BETWEEN 1 AND 10 EACH 1, IS NULL, OUTSIDE RANGE)

premdb=# insert into partition_test(c1) select seasonid from match;
INSERT 0 8606
premdb=# select * from sys.table_partition;
 table_id | n1 | n2 | n3 | n4 | count 
----------+----+----+----+----+-------
   16422 |  0 |  0 |  0 |  0 |     0
   16422 |  1 |  0 |  0 |  0 |   462
   16422 |  2 |  0 |  0 |  0 |   462
   16422 |  3 |  0 |  0 |  0 |   462
   16422 |  4 |  0 |  0 |  0 |   380
   16422 |  5 |  0 |  0 |  0 |   380
   16422 |  6 |  0 |  0 |  0 |   380
   16422 |  7 |  0 |  0 |  0 |   380
   16422 |  8 |  0 |  0 |  0 |   380
   16422 |  9 |  0 |  0 |  0 |   380
   16422 | 10 |  0 |  0 |  0 |   380
   16422 | 11 |  0 |  0 |  0 |  4560
   16422 | 12 |  0 |  0 |  0 |     0
(13 rows)

Parent topic:CREATE TABLE