CREATE TEMPORARY TABLE

Creates a table whose data persists only during the current session.

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

Syntax

Create with column definitions:

CREATE [ scope ] TEMP[ORARY] TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name 
   ( column-definition[,...] )
   [ table-constraint ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ NO PROJECTION ]
   [ ORDER BY table-column[,...] ]
   [ segmentation-spec ]
   [ KSAFE [k-num] ]
   [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]

Create from another table:


CREATE TEMP[ORARY] TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name
   [ ( column-name-list ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
AS  [ /*+ LABEL */ ] [ AT epoch ] query [ ENCODED BY column-ref-list ]

Parameters

scope

Specifies visibility of the table definition:

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

If no scope is specified, Vertica uses the default that is set by configuration parameter DefaultTempTableLocal.

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.

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 IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

[database.]schema

Database and schema. The default schema is public. 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 9800 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.

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:

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

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

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:

hash-segmentation-clause: Specifies to segment data evenly and distribute across cluster nodes. Vertica recommends segmenting large tables.
unsegmented-clause: Specifies to create an unsegmented projection.

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.

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  [ /*+ 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.

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

Examples

See Creating temporary tables.