Document `CREATE TEMP TABLE...` about duckdb-web HOT 3 CLOSED

Good questions! Here are a few answers and we can add more details to the docs as well.

The tables reside in memory, but I am not certain what happens if they exceed memory (@Mytherin
or @hannes happen to know?) . I believe that they would use a temporary file if they exceeded memory?

Temporary tables last for the duration of your connection to DuckDB, so they are removed when you disconnect.

There are a number of use cases, as they are present in many databases. It can be useful to materialize an intermediate computation temporarily for many reasons (maybe you want to reuse that result set multiple times within that connection like in your case, or index it before you join on it, or just simplify your query by splitting it into pieces). Does that help? I think they are a good fit for your use case!

from duckdb-web.

The tables reside in memory, but I am not certain what happens if they exceed memory (@Mytherin or @hannes happen to know?) . I believe that they would use a temporary file if they exceeded memory?

If a temp_directory is specified chunks of the temporary table can indeed be off-loaded to disk as required. If not then an error will be thrown.

from duckdb-web.

There are basically two styles of "temporary tables" out there: global and session scoped.

Global tables are permanent tables that have a globally defined schema, but the data is private to the session (e.g., Oracle). One nasty problem with them is that you can't use them for the kind of local analytics you are describing because the temporary table name is global. This is problematic for query generating systems (like Tableau, which is where I ran into this).

Fortunately, as a single user system, DuckDB supports session scoped temporary tables. Using them to cache expensive intermediate results such as rollups is a common use case, so go for it!

from duckdb-web.