Questions about temporary tables

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?


  • Options
    Navin_CNavin_C Vertica Customer
    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
  • Options
    Thanks, that really helps!
  • Options
    If you only use them in subqueries, WITH may also help: https://my.vertica.com/docs/6.1.x/HTML/index.htm#19410.htm
  • Options
    Exactly what I was looking for, thank you!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file