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.
CREATE TEMPORARY TABLE can create tables at two scopes, global and local, through the keywords
|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
You can specify whether temporary table data is transaction- or session-scoped:
ON COMMIT DELETE ROWS(default): Vertica automatically removes all table data when each transaction ends.
ON COMMIT PRESERVE ROWS: Vertica preserves table data across transactions in the current session. Vertica automatically truncates the table when the session ends.
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
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.
=> CREATE TEMPORARY TABLE tempDelete (a int, b int); CREATE TABLE => INSERT INTO tempDelete VALUES(1,2); OUTPUT -------- 1 (1 row) => SELECT * FROM tempDelete; a | b ---+--- 1 | 2 (1 row) => COMMIT; COMMIT => 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.
ON COMMIT PRESERVE ROWS
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.
=> CREATE TEMPORARY TABLE tempPreserve (a int, b int) ON COMMIT PRESERVE ROWS;
=> 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)
The following Eon Mode restrictions apply to temporary tables:
- K‑safety of temporary tables is always set to 0, regardless of system K‑safety. If a
CREATE TEMPORARY TABLEstatement sets k‑num greater than 0, Vertica returns an warning.
If subscriptions to the current session change, temporary tables in that session becomes inaccessible. Causes for session subscription changes include:
- A node left the list of participating nodes.
- A new node appeared in the list of participating nodes.
- An active node changed for one or more shards.
Note: Background mergeout operations have no effect on session subscriptions.