Questions about temporary tables

May_1

I'd like to use temp tables for storing results that will be used in sub-queries. Is there table variables in Vertica? Basically, I need to create the temp table(table variable) and insert data to be used in sub-queries, and after the main query is done, the temp table (table variable) should be gone . Is Vertica local temp table in memory or on disk? Does it clean up after itself (destroy itself) when the session is done?

Navin_C

Hello May, Vertica Local temporary tables are session scoped, that means they are only available for a particular session and when the session ends they are automatically dropped. Each local temporary table is visible only to the user who creates it, and only for the duration of the session in which the table is created. When the session ends, Vertica automatically drops the table definition from the database catalogs. Vertica Local temporary table are stored on disk in V_TEMP_SCHEMA namespace. Hope this helps

May_1

Thanks, that really helps!

Jaka_Jančar

If you only use them in subqueries, WITH may also help: https://my.vertica.com/docs/6.1.x/HTML/index.htm#19410.htm

May_1

Exactly what I was looking for, thank you!