CREATE TABLE

Creates a table in the logical schema.

Syntax

Create with column definitions:

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 from another table:

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[,…]

Invalid for external tables, 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.

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]

Invalid for external tables, 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.
partition‑clause

Invalid for external tables, logically divides table data storage through a PARTITION BY clause:

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

For details, see Partition Clause.
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:

  • INCLUDE [SCHEMA] PRIVILEGES specifies that the table inherits privileges that are set on its schema. This is the default behavior if privileges inheritance is enabled for the schema.
  • EXCLUDE [SCHEMA] PRIVILEGES disables inheritance of privileges from the schema.

For details, see Inherited Privileges in the Administrator's Guide.
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

    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.
  • 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

Non-superuser:

  • CREATE privileges on the table schema
  • If creating a table that includes a named sequence:
    • SELECT privilege on sequence object
    • USAGE privilege on sequence schema
  • If creating a table with the LIKE clause, source table owner

Examples

See Also