CREATE TEMPORARY TABLE
Creates a table whose data persists only during the current session. By default, 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 [safety-level] ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
[ DISK_QUOTA quota ]
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 ]
[ DISK_QUOTA quota ]
Parameters
scope
- 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 the DefaultTempTableLocal configuration parameter.
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
- Name of 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
- Column names and types. A table can have up to 9800 columns.
-
table-constraint
- Adds a constraint to table metadata.
ON COMMIT
- 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. TheORDER BY
clause cannot include qualifiersASC
orDESC
. Vertica always stores projection data in ascending sort order.If you omit the
`ORDER BY`
clause, Vertica uses theSELECT
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 [
safety-level
]
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 setsk-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.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 TEMP TABLE customer_occupations (name, profession) AS SELECT customer_name, occupation FROM customer_dimension;
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 isAUTO
.
This option and *
column-name-list
*are mutually exclusive. This option is invalid for external tables.-
DISK_QUOTA
quota
- String, an integer followed by a supported unit: K, M, G, or T. If the schema has a quota, this value must be smaller than the schema quota. Data-load 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.
Disk quota is valid for global temporary tables but not local ones.
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.
-
Local temporary tables cannot have disk quotas.
Examples
See Creating temporary tables.