Vertica Analytics Platform Version 9.2.x Documentation

Column-Definition

Specifies the name, data type, and constraints to be applied to a column.

Syntax

column-name data-type 
... [ column‑constraint ][…]
... [ ENCODING encoding-type ]
... [ ACCESSRANK integer ]

Parameters

column-name

The name of a column to be created or added.

data-type

One of the following data types:

  • BINARY
  • BOOLEAN
  • CHARACTER
  • DATE/TIME
  • NUMERIC

When specifying the maximum column width in a CREATE TABLE statement, use the width in bytes (octets) for any of the string types. Each UTF-8 character might require four bytes, but European languages generally require a little over one byte per character, while Oriental languages generally require a little under three bytes per character.

column‑constraint

Specifies a column constraint for this column.

ENCODING encoding-type

Specifies the column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

Example

The following example creates a table named Employee_Dimension and its associated superprojection in the Public schema. The Employee_key column is designated as a primary key, and RLE encoding is specified for the Employee_gender column definition:

=> CREATE TABLE Public.Employee_Dimension (
    Employee_key                   integer PRIMARY KEY NOT NULL,
    Employee_gender                varchar(8) ENCODING RLE,
    Courtesy_title                 varchar(8),
    Employee_first_name            varchar(64),
    Employee_middle_initial        varchar(8),
    Employee_last_name             varchar(64)
);