Partition Clause

Specifies partitioning of table data, through a PARTITION BY clause in the table definition:

PARTITION BY partition‑expression [ GROUP BY group‑expression ]

PARTITION BY partition‑expression

For each table row, resolves to a partition key that is derived from one or more table columns.

Caution: Avoid partitioning tables on LONG VARBINARY and LONG VARCHAR columns. Doing so can adversely impact performance.

GROUP BY group‑expression

For each table row, resolves to a partition group key that is derived from the partition key. Vertica uses group keys to merge partitions into separate partition groups. GROUP BY must use the same expression as PARTITION BY. For example:

...PARTITION BY (i+j) GROUP BY (
     CASE WHEN (i+j) < 5 THEN 1 
          WHEN (i+j) < 10 THEN 2 
          ELSE 3);

For details on partitioning table data by groups, see Partition Grouping and Hierarchical Partitioning in the Administrator's Guide.

Partitioning Requirements and Restrictions

PARTITION BY expressions can specify leaf expressions, functions, and operators. The following requirements and restrictions apply:

All table projections must include all columns referenced in the expression; otherwise, Vertica cannot resolve the expression.
The expression can reference multiple columns, but it must resolve to a single non-null value for each row.
All leaf expressions must be constants or table columns.
All other expressions must be functions and operators. The following restrictions apply to functions:
- They must be immutable—that is, they return the same value regardless of time and locale and other session- or environment-specific conditions.
- They cannot be aggregate functions.
- They cannot be Vertica meta-functions.
The expression cannot include queries.

GROUP BY expressions do not support modulo (%) operations.

Partition Clause

Partitioning Requirements and Restrictions

See Also