Vertica Analytics Platform Version 9.2.x Documentation

Column-Name-List

Used to rename columns when creating a table or temporary table from a query (CREATE TABLE AS SELECT); also used to specify the column's encoding type and access rank .

Syntax

column-name-list 
... [ ENCODING encoding‑type ] 
... [ ACCESSRANK integer ]
... [ GROUPED ( column‑reference[,…] ) ]

Parameters

column‑name Specifies the new name for the column.
ENCODING encoding‑type

Specifies the type of encoding to use on the column. The default encoding type is AUTO.

ACCESSRANK integer Overrides the default access rank for a column, useful for prioritizing access to a column. See Prioritizing Column Access Speed in the Administrator's Guide.
GROUPED Groups two or more columns . For detailed information, see GROUPED Clause.

Requirements

  • A column in the list can not specify the column's data type or any constraint. These are derived from the queried table.
  • If the query output has expressions other than simple columns (for example, constants or functions) then an alias must be specified for that expression, or the column name list must include all queried columns.
  • CREATE TABLE can specify encoding types and access ranks in the column name list or the query's ENCODED BY clause, but not in both. For example, the following CREATE TABLE statement sets encoding and access rank on two columns in the column name list:

    => CREATE TABLE promo1 (state ENCODING RLE ACCESSRANK 1, zip ENCODING RLE,…)
         AS SELECT * FROM customer_dimension ORDER BY customer_state;

    The next statement specifies the same encoding and access rank in the query's ENCODED BY clause.

    => CREATE TABLE promo2 
         AS SELECT * FROM customer_dimension ORDER BY customer_state
         ENCODED BY customer_state ENCODING RLE ACCESSRANK 1, customer_zip ENCODING RLE;