Creating Temporary Tables

CREATE TEMPORARY TABLE creates a table whose data persists only during the current session. Temporary table data is never visible to other sessions.

By default, all temporary table data is transaction-scoped—that is, the data is discarded when a COMMIT statement ends the current transaction. If CREATE TEMPORARY TABLE includes the parameter ON COMMIT PRESERVE ROWS, table data is retained until the current session ends.

Temporary tables can be used to divide complex query processing into multiple steps. Typically, a reporting tool holds intermediate results while reports are generated—for example, the tool first gets a result set, then queries the result set, and so on.

When you create a temporary table, Vertica automatically generates a default projection for it. For more information, see Auto-Projections.

Global versus Local Tables

CREATE TEMPORARY TABLE can create tables at two scopes, global and local, through the keywords GLOBAL and LOCAL, respectively:

Global temporary tables

Vertica creates global temporary tables in the public schema. Definitions of these tables are visible to all sessions, and persist across sessions until they are explicitly dropped. Multiple users can access the table concurrently. Table data is session-scoped, so it is visible only to the session user, and is discarded when the session ends.

Local temporary tables

Vertica creates local temporary tables in the V_TEMP_SCHEMA namespace and inserts them transparently into the user's search path. These tables are visible only to the session where they are created. When the session ends, Vertica automatically drops the table and its data.

Data Retention

You can specify whether temporary table data is transaction- or session-scoped:

Note: If you create a temporary table with ON COMMIT PRESERVE ROWS, you cannot add projections for that table if it contains data. You must first remove all data from that table with TRUNCATE TABLE.

You can create projections for temporary tables created with ON COMMIT DELETE ROWS, whether populated with data or not. However, CREATE PROJECTION ends any transaction where you might have added data, so projections are always empty.

By default, Vertica removes all data from a temporary table, whether global or local, when the current transaction ends.

For example:

=> CREATE TEMPORARY TABLE tempDelete (a int, b int);
=> INSERT INTO tempDelete VALUES(1,2);
(1 row)

=> SELECT * FROM tempDelete;
 a | b
 1 | 2
(1 row)


=> SELECT * FROM tempDelete;
 a | b
(0 rows)

If desired, you can use DELETE within the same transaction multiple times, in order to refresh table data repeatedly.

You can specify that a temporary table retain data across transactions in the current session, by defining the table with the keywords ON COMMIT PRESERVE ROWS. Vertica automatically removes all data from the table only when the current session ends.

For example:

=> INSERT INTO tempPreserve VALUES (1,2); OUTPUT -------- 1 (1 row) => COMMIT; COMMIT => SELECT * FROM tempPreserve; a | b ---+--- 1 | 2 (1 row) => INSERT INTO tempPreserve VALUES (3,4); OUTPUT -------- 1 (1 row) => COMMIT; COMMIT => SELECT * FROM tempPreserve; a | b ---+--- 1 | 2 3 | 4 (2 rows)

Eon Restrictions

The following Eon Mode restrictions apply to temporary tables: