CREATE PROJECTION

Creates metadata for a in the Vertica catalog.

Creates metadata for a projection in the Vertica catalog. Vertica supports four types of projections:

  • Standard projection: Stores a collection of table data in a format that optimizes execution of certain queries on that table.

  • Live aggregate projection: Stores the grouped results of queries that invoke aggregate functions (such as SUM) on table columns.

  • Top-K projection: Stores the top k rows from partitions of selected rows.

  • UDTF projection: Stores newly-loaded data after it is transformed and/or aggregated by user-defined transformation functions (UDTFs).

Complex data types have additional restrictions when used within a projection:

  • Each projection must include at least one column that is a primitive type or native array.

  • An AS SELECT clause can use a complex-type column, but any other expression must be of a scalar type or native array.

  • The ORDER BY, PARTITION BY, and GROUP BY clauses cannot use complex types.

  • If a projection does not include an ORDER BY or segmentation clause, Vertica uses only the primitive columns from the select list to order or segment data.

  • Projection columns cannot be complex types returned from functions such as ARRAY_CAT.

  • TopK and UDTF projections do not support complex types.

In this section