Table Schemas for Global and Local Temp Tables

Posted May 8, 2019 by Jim Knicely, Vertica Principal Solution Architect

Vintage businessman concept wearing futuristic helmet at office
By default, Vertica stores Global Temporary Tables in the Public schema and Local Temporary Tables in the V_TEMP_SCHEMA schema.

Example:

Vertica uses the default schemas if a schema name is not provided when creating temporary tables: dbadmin=> CREATE GLOBAL TEMP TABLE temp_global (c1 INT); CREATE TABLE dbadmin=> CREATE LOCAL TEMP TABLE temp_local (c1 INT); CREATE TABLE dbadmin=> SELECT table_name, table_schema dbadmin-> FROM tables dbadmin-> WHERE table_name IN ('temp_global', 'temp_local'); table_name | table_schema -------------+--------------- temp_global | public temp_local | v_temp_schema (2 rows) I can specify a different schema name for a Global Temporary Table: dbadmin=> CREATE SCHEMA global_table_schema; CREATE SCHEMA dbadmin=> CREATE GLOBAL TEMP TABLE global_table_schema.temp_global (c1 INT); CREATE TABLE dbadmin=> SELECT table_name, table_schema dbadmin-> FROM tables dbadmin-> WHERE table_name IN ('temp_global'); table_name | table_schema -------------+--------------------- temp_global | public temp_global | global_table_schema (2 rows) But not for a Local Temporary Table: dbadmin=> CREATE SCHEMA local_table_schema; CREATE SCHEMA dbadmin=> CREATE LOCAL TEMP TABLE local_table_schema.temp_local (c1 INT); ROLLBACK 5948: Local temporary objects may not specify a schema name Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/CreatingTemporaryTables.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEMPORARYTABLE.htm

Have fun!