CREATE TEMPORARY TABLE

scope

Specifies visibility of the table definition:

• GLOBAL (default): The table definition is visible to all sessions, and persists until you explicitly drop the table.
• LOCAL: the table definition is visible only to the session in which it is created, and is dropped when the session ends.

Regardless of this setting, retention of temporary table data is set by the keywords ON COMMIT DELETE and ON COMMIT PRESERVE (see below).

For more information, see Creating Temporary Tables in the Administrator's Guide.

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.

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject

If you do not specify a schema, the table is created in the default schema.

Identifies the name of the table to create, where table-nameconforms to conventions described in Identifiers.

Defines a table column. A table can have up to 1600 columns.

Adds a constraint to table metadata.

Specifies whether data is transaction- or session-scoped:

ON COMMIT {PRESERVE | DELETE} ROWS

• DELETE (default) marks the temporary table for transaction-scoped data. Vertica removes all table data after each commit.
• PRESERVE marks the temporary table for session-scoped data, which is preserved beyond the lifetime of a single transaction. Vertica removes all table data when the session ends.

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.

Prevents Vertica from creating auto-projections for this table. A superprojection is created only when data is explicitly loaded into this table.

NO PROJECTION is invalid with the following clauses:

• ORDER BY
• KSAFE
• Any segmentation clause (hash-segmentation-clause or unsegmented‑clause).

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.

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.

Invalid for external tables, specifies how to distribute data for auto-projections of this table. Supply one of the following clauses:

• hash‑segmentation‑clause: Specifies to segment data evenly and distribute across cluster nodes. Vertica recommends segmenting large tables. For details, see Hash Segmentation Clause.
• unsegmented‑clause: Specifies to create an unsegmented projection. For details, see Unsegmented Clause.

If this clause is omitted, Vertica generates auto-projections with default hash segmentation.

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.

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

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.

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