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 ][,...] ) [ 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 ) ] [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ] AS [ /*+ LABEL */ ] [ AT epoch ] query [ ENCODED BY column-ref-list ] [ segmentation‑spec ]
LIKE‑clause
LIKE [[database.]schema.]existing-table [ {INCLUDING | EXCLUDING} PROJECTIONS ] [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
Parameters
IF NOT EXISTS
|
If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists. The For related information, see ON_ERROR_STOP. |
[database.]schema
|
Database and schema. The default schema is |
table |
Name of the table to create, which must be unique among names of all sequences, tables, projections, views, and models within the schema. |
column-definition |
Column name, data type, and optional constraints. A table can have up to 9800 columns. At least one column in the table must be of a scalar type or native array. |
table‑constraint |
Table-level constraint, as opposed to column constraints. |
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. |
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 ] |
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}
|
Default inheritance of schema privileges for this table:
For details, see Inherited Privileges. |
AS query
|
Creates and loads a table from the results of a query, specified as follows: The query cannot include complex type columns. |
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 LIKE clause with one of the following options:
|
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
The following example creates a table in the public schema:
CREATE TABLE public.Premium_Customer ( ID IDENTITY , lname varchar(25), fname varchar(25), store_membership_card int );
The following example uses LIKE to create a new table from this one:
=> CREATE TABLE All_Customers LIKE Premium_Customer; CREATE TABLE
The following example selects columns from one table to use in a new table, using an AS clause:
=> CREATE TABLE cust_basic_profile AS SELECT customer_key, customer_gender, customer_age, marital_status, annual_income, occupation FROM customer_dimension WHERE customer_age>18 AND customer_gender !=''; CREATE TABLE => SELECT customer_age, annual_income, occupation FROM cust_basic_profile WHERE customer_age > 23 ORDER BY customer_age; customer_age | annual_income | occupation --------------+---------------+-------------------- 24 | 469210 | Hairdresser 24 | 140833 | Butler 24 | 558867 | Lumberjack 24 | 529117 | Mechanic 24 | 322062 | Acrobat 24 | 213734 | Writer ...
The following example creates a table using array columns:
=> CREATE TABLE orders( orderkey INT, custkey INT, prodkey ARRAY[VARCHAR(10)], orderprices ARRAY[DECIMAL(12,2)], orderdate DATE );
The following example uses a ROW complex type:
=> CREATE TABLE inventory (store INT, products ROW(name VARCHAR, code VARCHAR));