CREATE TABLE

Creates a table in the logical schema.

Creates a table in the logical schema. You can specify the column definitions directly, or you can base a table on an existing table using AS or LIKE.

Syntax

Create with column definitions

CREATE TABLE [ IF NOT EXISTS ] [[ { namespace. | database. } ]schema.]table
   ( column-definition[,...] [, table-constraint [,...]] )
   [ ORDER BY column[,...] ]
   [ segmentation-spec ]
   [ KSAFE [safety] ]
   [ partition-clause]
   [ { INCLUDE | EXCLUDE } [SCHEMA] PRIVILEGES ]
   [ DISK_QUOTA quota ]

Create from another table

CREATE TABLE [ IF NOT EXISTS ] [[{ namespace. | database. } ]schema.]table
   { AS-clause | LIKE-clause }
   [ DISK_QUOTA quota ]

AS-clause:

[ ( column-name-list ) ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
AS  [ /*+ LABEL */ ] [ AT epoch ] query [ ENCODED BY column-ref-list ] [ segmentation-spec ]

LIKE-clause:

LIKE [[{ namespace. | database. } ]schema.]existing-table
  [ { INCLUDING | EXCLUDING } PROJECTIONS ]
  [ { INCLUDE | EXCLUDE } [SCHEMA] PRIVILEGES ]

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

namespace
For Eon Mode databases, name of the namespace under which to create the table. If unspecified, the table is created under the default_namespace.
database
For Enterprise Mode databases, name of the database. If specified, it must be the current database.
schema
Name of the schema under which to create the table. If no schema is specified, the table is created in the public schema.
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 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 [ safety ]

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 (AS query), defines column names that map to the query output. If you omit this list, Vertica uses the query output column names.

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

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;
{INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES

Default inheritance of schema privileges for this table:

  • INCLUDE 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 PRIVILEGES disables inheritance of privileges from the schema.

For details, see Inherited privileges.

AS query

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

AS  [ /*+ LABEL */ ] [ AT  ] epoch query

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:

  • 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. The user issuing the CREATE TABLE LIKE statement owns the new table, not the original owner.

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.

  • {INCLUDE|EXCLUDE} [SCHEMA] PRIVILEGES: See description above).

DISK_QUOTA quota
String, an integer followed by a supported unit: K, M, G, or T. Data-load, DML, and ILM operations that increase the table's usage beyond the set quota fail. For details, see Disk quotas.

If not specified, the table has no quota.

Privileges

Superuser to set disk quota.

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

Restrictions for complex types

Complex types used in native tables have some restrictions, in addition to the restrictions for individual types listed on their reference pages:

  • A native table must have at least one column that is a primitive type or a native array (one-dimensional array of a primitive type). If a flex table has real columns, it must also have at least one column satisfying this restriction.

  • Complex type columns cannot be used in ORDER BY or PARTITION BY clauses nor as FILLER columns.

  • Complex type columns cannot have constraints.

  • Complex type columns cannot use DEFAULT or SET USING.

  • Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.

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
);

In an Eon Mode database, the following example creates the same table from the previous example, but in the store schema of the company namespace:

CREATE TABLE company.store.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 the Premium_Customer table:

=> 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));

The following example uses quotas:

=> CREATE SCHEMA internal DISK_QUOTA '10T';
CREATE SCHEMA

=> CREATE TABLE internal.sales (...) DISK_QUOTA '5T';
CREATE TABLE

=> CREATE TABLE internal.leads (...) DISK_QUOTA '12T';
WARNING 0: Table leads has disk quota greater than its schema internal

The following statement creates a flights table in the airline schema under the airport namespace:

=> CREATE TABLE airport.airline.flights(
    flight_number INT,
    leaving_from VARCHAR,
    arriving_at VARCHAR,
    expected_departure DATE,
    gate VARCHAR
);

See also

In this section