Table Schemas for Global and Local Temp Tables

Jim_Knicely

By default Vertica will store 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/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/CreatingTemporaryTables.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEMPORARYTABLE.htm

Have fun!