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 }
AS‑clause
[ ( column-name-list ) ] [ load-method ] [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ] AS [ /*+ hint[, hint] */ ] [ AT epoch ] query [ ENCODED BY column-ref-list ]
LIKE‑clause
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 The For related information, see |
[database.]schema |
Specifies a schema, by default 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
For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect. For details, see Choosing a Load Method in the Administrator's Guide. |
ORDER BY column[,…] |
Invalid for external tables, specifies columns from the If you omit the |
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 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 ( For example: CREATE TABLE customer_occupations (name, profession) AS SELECT customer_name, occupation FROM customer_dimension; This clause and the |
{ INCLUDE | EXCLUDE } [SCHEMA] PRIVILEGES |
Specifies default inheritance of schema privileges for this table:
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
You cannot use SELECT AS to query tables containing complex types, even if you do not select the columns with those types. Complex types are supported only in external tables. See External Types. For details, see Creating a Table from a Query in the Administrator's Guide. |
ENCODED BY column‑ref‑list |
A comma-delimited list of columns from the source table, where each column is qualified by one or both of the following encoding options:
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
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 objectUSAGE
privilege on sequence schema
- If creating a table with the
LIKE
clause, source table owner