PARTITION BY

PARTITION BY

PARTITION BY is a subcommand of ALTER TABLE that is used to define partitions and subpartitions on a table, and repartition or unpartition a table.

Note:

Defining table partitions is an enterprise-only feature. If you are looking for the PARTITION BY used in SQL window functions, see Window Functions.

Tip:

New in v19.1: This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

Primary key requirements

The primary key required for partitioning is different from the conventional primary key: The unique identifier in the primary key requires to be prefixed with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions.

As of CockroachDB v2.0, you cannot alter the primary key after it has been defined while creating the table. If the primary key in your existing table does not meet the requirements, you will not be able to use the ALTER TABLE statement to define partitions or subpartitions on the existing table.

Synopsis

Parameters

Parameter	Description
`table_name`	The name of the table you want to define partitions for.
`name_list`	List of columns you want to define partitions on (in the order they are defined in the primary key).
`list_partitions`	Name of list partition followed by the list of values to be included in the partition.
`range_partitions`	Name of range partition followed by the range of values to be included in the partition.

Required privileges

The user must have the CREATE privilege on the table.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Define a list partition on an existing table

Suppose we have an existing table named students_by_list in a global online learning portal, and the primary key of the table is defined as (country, id). We can define partitions on the table by list:

> ALTER TABLE students_by_list PARTITION BY LIST (country)
      (PARTITION north_america VALUES IN ('CA','US'),
      PARTITION australia VALUES IN ('AU','NZ'),
      PARTITION DEFAULT VALUES IN (default));

Define a range partition on an existing table

Suppose we have an another existing table named students_by_range and the primary key of the table is defined as (expected_graduation_date, id). We can define partitions on the table by range:

> ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date)
      (PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
      PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE));

Define a subpartitions on an existing table

Suppose we have an yet another existing table named students with the primary key defined as (country, expected_graduation_date, id). We can define partitions and subpartitions on the table:

> ALTER TABLE students PARTITION BY LIST (country)(
        PARTITION australia VALUES IN ('AU','NZ') PARTITION BY RANGE (expected_graduation_date)(PARTITION graduated_au VALUES FROM (MINVALUE) TO ('2017-08-15'), PARTITION current_au VALUES FROM ('2017-08-15') TO (MAXVALUE)),
        PARTITION north_america VALUES IN ('US','CA') PARTITION BY RANGE (expected_graduation_date)(PARTITION graduated_us VALUES FROM (MINVALUE) TO ('2017-08-15'), PARTITION current_us VALUES FROM ('2017-08-15') TO (MAXVALUE))
    );

Repartition a table

> ALTER TABLE students_by_range PARTITION BY RANGE (expected_graduation_date) (
    PARTITION graduated VALUES FROM (MINVALUE) TO ('2018-08-15'),
    PARTITION current VALUES FROM ('2018-08-15') TO (MAXVALUE));

Unpartition a table

> ALTER TABLE students PARTITION BY NOTHING;

PARTITION BY (Enterprise)