CLUSTER BY

The CLUSTER BY command is used for changing clustering keys in a table.

For more information, see the following:

Syntax

The following is the correct syntax for the CLUSTER BY command:

alter_table_rename_table_statement ::=
    ALTER TABLE [schema_name.]table_name CLUSTER BY column_name [, ...]
    ;

create_table_statement ::=
    CREATE [ OR REPLACE ] TABLE [schema_name.]table_name (
        { column_def [, ...] }
    )
    [ CLUSTER BY { column_name [, ...] } ]
    ;

column_def :: = { column_name type_name [ default ] [ column_constraint ] }

table_name ::= identifier

column_name ::= identifier

Note

SQream does not support clustering by TEXT columns.

Parameters

The following table shows the CLUSTER BY parameters:

Parameter

Description

schema_name

The schema name for the table. Defaults to public if not specified.

OR REPLACE

Creates a new tables and overwrites any existing table by the same name. Does not return an error if the table already exists. CREATE OR REPLACE does not check the table contents or structure, only the table name.

column_def

A comma separated list of column definitions. A minimal column definition includes a name identifier and a datatype. Other column constraints and default values can be added optionally.

table_name

The table name to apply the change to.

column_name [, ... ]

Comma separated list of columns to create clustering keys for.

Usage Notes

Removing clustering keys does not affect existing data.

To force data to re-cluster, the table has to be recreated (i.e. with CREATE TABLE).

Example

Reclustering a Table

The following example shows how to recluster a table:

ALTER TABLE public.users CLUSTER BY start_date;

Permissions

The role must have the DDL permission at the database or table level.