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.

Neither the data type nor column constraint can be specified for a column in the column-name-list. These are derived by the columns in the query table identified in the FROM clause. If the query output has expressions other than simple columns (for example, constants or functions) then either an alias must be specified for that expression, or all columns must be listed in the column name list.

You can supply the encoding type and access rank in either the column-name-list or the column list in the query, but not both.

Examples

The following statements are both allowed (also allowed for CREATE TEMP TABLE):

=> CREATE TABLE promo (state ENCODING RLE ACCESSRANK 1, zip ENCODING RLE, ...)
   AS SELECT * FROM customer_dimension 
   ORDER BY customer_state, ... ;
=> CREATE TABLE promo 
   AS SELECT * FROM customer_dimension 
   ORDER BY customer_state
   ENCODED BY customer_state ENCODING RLE ACCESSRANK 1, customer_zip ENCODING RLE ...;

The following statement is not allowed because encoding is specified in both column‑name‑list and ENCODED BY clause:

=> CREATE TABLE promo (state ENCODING RLE ACCESSRANK 1, zip ENCODING RLE, ...)
   AS SELECT * FROM customer_dimension 
   ORDER BY customer_state
   ENCODED BY customer_state ENCODING RLE ACCESSRANK 1, customer_zip ENCODING RLE ...;

Examples

The following examples show CREATE TABLE. The explanations are also true for CREATE TEMP TABLE.

The following example creates a table named employee_dimension and its associated superprojection in the public schema. Note that encoding-type RLE 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)
);

Using the VMart schema, the following example creates a table named promo from a query that selects data from columns in the customer_dimension table. RLE encoding is specified for the state column in the column name list.

=> CREATE TABLE promo (
     name, 
     address, 
     city, 
     state ENCODING RLE, income ) 
   AS SELECT customer_name, 
     customer_address, 
     customer_city,
     customer_state, 
     annual_income 
FROM customer_dimension 
WHERE annual_income > 1000000 
ORDER BY customer_state, annual_income;