This topic describes how to create a list partitioned table.

Syntax

The following statement is used to create one or more list partitioned tables. List partitions are based on enumerated values. Therefore, you must enumerate the values of the partition keys for each partition. The enumerated values must be unique. List partitions support the extended data type of LIST COLUMNS. 

CREATE TABLE ... PARTITION BY LIST {(expr)  COLUMNS(column_list)}
(partition_definition [, partition_definition] ...);
partition_definition is:
PARTITION partition_name
    VALUES IN (value_list)

Parameters

Parameter Description
expr The expression of the partition. It must be of the INT type. The string type is not supported.
column_list The list of partition key columns. It is used in LIST COLUMNS(). Expressions are not supported.
value_list The boundary value of the partition.
partition_name The name of the partition. The name must be unique within the table.

Description

LIST supports expressions. The return data of a LIST expression must be of the INT type.

LIST supports only single-column partition keys.

LIST COLUMNS does not support expressions, but supports columns.

LIST COLUMNS supports multi-column partition keys. LIST COLUMNS supports partition keys of the following data types: INT, string types, DATE, and DATETIME.

Examples

Create a list partitioned table:
CREATE TABLE sales_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (amount)
(
  PARTITION p0 VALUES in (1, 2),
  PARTITION p1 VALUES in (3, 4),
  PARTITION p2 VALUES in (5, 6)
);
Create a list columns partitioned table:
CREATE TABLE sales_list_columns
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS(country)
(
  PARTITION europe VALUES in ('FRANCE', 'ITALY'),
  PARTITION asia VALUES in ('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES in ('US', 'CANADA')
);