The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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?

Comments

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.