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!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
