CREATE TABLE

Creates a table in the logical schema.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table
... ( column‑definition[,... ] ) 
... [ table-constraint ]
... [ load-method ]
... [ ORDER BY column[,... ] ] 
... [ segmentation‑spec ] 
... [ KSAFE [k‑num] ]
... [ partition-clause]
... [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
CREATE TABLE [ IF NOT EXISTS ] [[database.]schema.]table { 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 [[database.]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.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
table

Identifies the table to create, where table conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
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 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‑clause

Logically divides table data storage through a PARTITION BY clause:

PARTITION BY partition‑expression [ GROUP BY group‑expression ]

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:

  • EXCLUDING PROJECTIONS (default): Do not copy projections from the source table.
  • INCLUDING PROJECTIONS: Copy current projections from the source table for the new table.
  • 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

In the Administrator's Guide, see:

See Also