Table Schemas for Global and Local Temp Tables
Jim_Knicely
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