CREATE TEMPORARY TABLE

Creates a table whose data persists only during the current session. Temporary table data is not visible to other sessions.

Syntax

Parameters

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.

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.

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

table-name

Identifies the table to create, where table-name 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.

ON COMMIT

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

NO PROJECTION

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:

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

ORDER BY table‑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.

Eon Mode: K‑safety of temporary tables is always set to 0, regardless of system K‑safety. If a CREATE TEMPORARY TABLE statement sets k‑num greater than 0, Vertica returns an warning.

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

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

Privileges

The following privileges are required:

  • CREATE privileges on the table schema
  • If creating a temporary table that includes a named sequence:
    • SELECT privilege on sequence object
    • USAGE privilege on sequence schema

Restrictions

  • Queries on temporary tables are subject to the same restrictions on SQL support as persistent tables.
  • You cannot add projections to non-empty, global temporary tables (ON COMMIT PRESERVE ROWS). Make sure that projections exist before you load data. See Auto-Projections in the Administrator's Guide.
  • While you can add projections for temporary tables that are defined with ON COMMIT DELETE ROWS specified, be aware that you might lose all data.
  • Moveout and mergeout operations cannot be used on session-scoped temporary data.
  • In general, session-scoped temporary table data is not visible using system (virtual) tables.
  • Temporary tables do not recover. If a node fails, queries that use the temporary table also fail. Restart the session and populate the temporary table.

See Also