All Products
Search

This Product

    PolarDB:LIST partitioning

    Document Center

    PolarDB:LIST partitioning

    Last Updated:Mar 28, 2026

    LIST partitioning assigns rows to partitions based on explicit value lists. Each partition holds rows whose partition key value appears in a defined VALUES IN list. Without a DEFAULT partition, inserting a row whose key value is not covered by any partition returns an error. Add a DEFAULT partition to capture all unmatched rows.

    For a comparison of LIST partitioning and LIST COLUMNS partitioning, see the Comparison between LIST partitioning and LIST COLUMNS partitioning table in the "Overview" topic.

    When to use LIST partitioning

    LIST partitioning works well when partition key values fall into a finite, known set — for example, region codes, category IDs, or status flags. If the set of distinct values is likely to grow continuously, consider HASH partitioning instead, because adding a new partition for each new value quickly becomes impractical.

    Syntax

    CREATE TABLE ...
PARTITION BY LIST(partition_expr) [PARTITIONS number]
(
  PARTITION part_name VALUES IN (list_bound_value_set),
  PARTITION part_name VALUES IN (list_bound_value_set),
  ...
)

partition_expr:
    partition_column
  | partition_func(partition_column)

list_bound_value_set:
  list_bound_value[, list_bound_value, ...]

-- Supported partitioning functions
partition_func:
    TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING
  | RIGHT
  | LEFT

    Key syntax elements:

    ElementDescription
    partition_exprA column name or a supported partitioning function applied to a column
    list_bound_value_setOne or more comma-separated values that map to this partition
    DEFAULTCatch-all value that routes all unmatched rows to this partition

    Limits

    LimitDefault value
    Maximum partitions per table8,192
    Maximum columns in a partition key5
    Maximum partition name length16 characters

    Additional constraints:

    • Partitioning functions are supported for single-column partition keys. The supported data types depend on the function used.

    • Partitioning functions are not supported for vector partition keys (multi-column partition keys).

    • Nested partitioning functions are not supported.

    • Partition names must be unique within a table.

    • If the partition key column uses a time zone-sensitive type such as TIMESTAMP, use the UNIX_TIMESTAMP partitioning function.

    Supported data types

    The following data types are supported as partition key column types:

    CategoryData types
    IntegerBIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED
    Date and timeDATETIME, DATE, TIMESTAMP
    StringCHAR, VARCHAR

    Examples

    Single-column partition key with a partitioning function

    The following example uses the birthday column of type DATETIME as the partition key. The TO_DAYS function converts date values to day counts, and rows are assigned to partitions based on specific dates. The pm partition uses DEFAULT to capture any rows with dates not listed in p1, p2, or p3 — without pm, inserting such a row would return an error.

    CREATE TABLE tb_l_fn(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime not null,
  primary key(id)
)
PARTITION BY LIST(TO_DAYS(birthday))
(
  PARTITION p1 VALUES IN (TO_DAYS('2020-01-01'), TO_DAYS('2020-02-01')),
  PARTITION p2 VALUES IN (TO_DAYS('2021-01-01'), TO_DAYS('2021-02-01')),
  PARTITION p3 VALUES IN (TO_DAYS('2022-01-01')),
  PARTITION pm VALUES IN (DEFAULT)
)

    For other supported partitioning functions, see Partitioning functions.

    Single-column partition key without a partitioning function

    The following example uses the id column as the partition key, assigning specific ID values to three named partitions. Rows with IDs not listed in any partition go to the default partition pm.

    CREATE TABLE tb_l(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime not null,
  primary key(id)
)
PARTITION BY LIST(id)
(
  PARTITION p1 VALUES IN (1000, 1001, 1002),
  PARTITION p2 VALUES IN (2000, 2001, 2002),
  PARTITION p3 VALUES IN (3000),
  PARTITION pm VALUES IN (DEFAULT)
)

    Vector partition key (multi-column)

    When you use multiple columns as the partition key with PARTITION BY LIST, PolarDB for Xscale automatically converts the statement to LIST COLUMNS partitioning.

    The following example uses birthday and id as a two-column partition key:

    -- Original statement using LIST with a vector partition key
CREATE TABLE tb_l(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime not null,
  primary key(id)
)
PARTITION BY LIST(birthday, id)
(
  PARTITION p1 VALUES IN (('1990-04-03', 1000), ('1991-04-03', 2000)),
  PARTITION p2 VALUES IN (('2000-01-03', 3000), ('2001-04-03', 3001)),
  PARTITION pm VALUES IN (DEFAULT)
)

    PolarDB for Xscale converts the preceding statement to the equivalent LIST COLUMNS definition:

    -- Equivalent LIST COLUMNS partitioning after automatic conversion
CREATE TABLE tb_l(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime not null,
  primary key(id)
)
PARTITION BY LIST COLUMNS(birthday, id)
(
  PARTITION p1 VALUES IN (('1990-04-03', 1000), ('1991-04-03', 2000)),
  PARTITION p2 VALUES IN (('2000-01-03', 3000), ('2001-04-03', 3001)),
  PARTITION pm VALUES IN (DEFAULT)
)

    What's next