Table Schemas for Global and Local Temp Tables
Jim_Knicely
- Select Field - Administrator
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!
0