CREATE TABLE

Creates a table in the logical schema. For information about creating temporary tables and external tables, see CREATE TEMPORARY TABLE and CREATE EXTERNAL TABLE AS COPY, respectively.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [schema.]table-name 
... ( column‑definition[,... ] ) 
... [ table-constraint ]
... [ load-method ]
... [ ORDER BY table-column[,... ] ] 
... [ segmentation‑spec ] 
... [ KSAFE [k‑num] ]
... [ PARTITION BY partition-expression]
... [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
CREATE TABLE [ IF NOT EXISTS ] [schema.]table-name { AS‑clause | LIKE‑clause }
... [ ( column-name-list ) ]
... [ load-method ]
... [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]  
AS  [  /*+ hint[, hint] */ ] [ AT epoch ] query [ ENCODED BY column‑ref‑list ]
LIKE [schema.]existing-table 
...[ {INCLUDING | EXCLUDING} PROJECTIONS ]
...[ load-method ]
...[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]

Parameters

IF NOT EXISTS

Specifies to generate an informational message if an object already exists under the specified name. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist, and reuse the existing object if it does.

For related information, see ON_ERROR_STOP.

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject

If you do not specify a schema, the table is created in the default schema.
table-name

Identifies the name of the table to create, where table-name conforms to conventions described in Identifiers.

column-definition

Defines a table column. A table can have up to 1600 columns.

table‑constraint

Adds a constraint to table metadata.
load-method

Specifies default load behavior for all DML operations on this table, such as INSERT and COPY, one of the following:

  • AUTO (default): Initially loads data into WOS, suitable for smaller bulk loads.
  • DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads.
  • TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.

For details, see Choosing a Load Method in the Administrator's Guide.
ORDER BY table‑column[,...]

Specifies columns from the SELECT list on which to sort the superprojection that is automatically created for this table. The ORDER BY clause cannot include qualifiers ASC or DESC. Vertica always stores projection data in ascending sort order.

If you omit the ORDER BY clause, Vertica uses the SELECT list order as the projection sort order.

This option is invalid for external tables.
segmentation‑spec

Invalid for external tables, specifies how to distribute data for auto-projections of this table. Supply one of the following clauses:

If this clause is omitted, Vertica generates auto-projections with default hash segmentation.
KSAFE [k‑num]

Specifies K-safety of auto-projections created for this table, where k-num must be equal to or greater than system K-safety. If you omit this option, the projection uses the system K-safety level. For general information, see K-Safety in Vertica Concepts.

Note: This option is invalid for external tables.
PARTITION BY partition‑expression

Logically divides table data storage, where partition‑expression resolves to a value derived from one or more table columns. For details, see Partition Clause.

This option is invalid for external tables.
column‑name‑list

Valid only when creating a table from a query (AS query), defines column names that map to the query output. If you omit this list, Vertica uses the query output column names. The names in column-name-list and queried columns must be the same in number.

For example:

CREATE TABLE customer_occupations (name, profession) 
   AS SELECT customer_name, occupation FROM customer_dimension;

This clause and the ENCODED BY clause are mutually exclusive. Column name lists are invalid for external tables

{ INCLUDE
| EXCLUDE}
[SCHEMA] PRIVILEGES

Specifies default inheritance of schema privileges for this table:

  • EXCLUDE [SCHEMA] PRIVILEGES (default) disables inheritance of privileges from the schema
  • INCLUDE [SCHEMA] PRIVILEGES grants the table the same privileges granted to its schema

For more information see Grant Inherited Privileges.
AS query

Creates and loads a table from the results of a query, specified as follows:

AS  [ /*+hint[, hint]*/ ] [ AT epoch ] query

You can qualify the AS clause with one or both of the following hints:

  • A load method hint: AUTO, DIRECT, or TRICKLE

    Note: The CREATE TABLE statement can also specify a load method. However, this load method applies to load operations only after the table is created.

  • LABEL

For details, see Creating a Table from a Query in the Administrator's Guide.
ENCODED BY column‑ref‑list

A list of columns from the source table, where each column is qualified by one or both of the following encoding options:

  • 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.
  • ENCODING encoding-type: Specifies the type of encoding to use on the column. The default encoding type is AUTO.

This option and column-name-list are mutually exclusive. This option is invalid for external tables
LIKE existing-table

Creates the table by replicating an existing table. You can qualify the LIKE clause with one of the following options:

  • {INCLUDING|EXCLUDING} PROJECTIONS: Specifies whether to copy projections from the source table:

    • EXCLUDING PROJECTIONS (default): Do not copy projections from the source table.
    • INCLUDING PROJECTIONS: Copy current projections from the source table for the new table. INCLUDING PROJECTIONS also copies all table constraints except foreign key constraints. If the table is associated with a storage policy, the policy association is also replicated.
  • load‑method: See description above.
  • {INCLUDE|EXCLUDE} [SCHEMA] PRIVILEGES: See description above).

For details, see Replicating a Table in the Administrator's Guide.

Privileges

The following privileges are required:

Examples

See Creating Tables in the Administrator's Guide.

See Also