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:

If you are looking for the PARTITION BY used in SQL window functions, see Window Functions.

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

ALTER TABLE IF EXISTS table_name PARTITION BY LIST ( name_list ) ( list_partitions RANGE ( name_list ) ( range_partitions ) NOTHING

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.

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:

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> 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

copy
icon/buttons/copy
> 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

copy
icon/buttons/copy
> ALTER TABLE students PARTITION BY NOTHING;

See also



Yes No