Temporary table
A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist.
Overview
Temporary tables behave like regular tables — you can INSERT, UPDATE, SELECT, and index them — but they live only for the duration of the session (or, in some engines, the transaction) that created them, and are invisible to other sessions. They're useful for staging intermediate results in a multi-step ETL script, breaking up a complex query into named pieces, or avoiding the need to clean up scratch tables manually.
Copy code
CREATE TEMPORARY TABLE staging_orders AS
SELECT * FROM read_csv('incoming_orders.csv');
UPDATE staging_orders SET status = 'pending' WHERE status IS NULL;
INSERT INTO orders SELECT * FROM staging_orders WHERE status = 'pending';
-- staging_orders is dropped automatically when the session ends
Temporary table vs CTE vs view
A CTE (WITH ... AS (...)) exists only for the duration of a single query and can't be indexed or reused across statements. A VIEW persists indefinitely and re-runs its query on every read. A temporary table sits between the two: it persists across multiple statements within a session (letting you build on intermediate results step by step) but disappears automatically when the session closes, requiring no manual cleanup.
DuckDB specifics
DuckDB supports both CREATE TEMP TABLE and CREATE TEMPORARY TABLE (equivalent keywords), and they work whether you're connected to an in-memory database or a persistent DuckDB file — temporary tables and their data are always held in memory regardless of the main database's storage, and are dropped when the connection closes. This makes them a lightweight scratch space for multi-step scripts (e.g., DuckDB running as an ELT engine) without writing intermediate results to disk.
Related terms
A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
CREATE TABLE AS SELECT (CTAS) →CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.
Common Table Expressions (CTEs) →A Common Table Expression (CTE) is a temporary named result set within a SQL query. Learn basic CTEs, chaining multiple CTEs, and recursive CTEs for hierarchical data.
Wide table →A wide table is a table with a large number of columns, typically produced by denormalizing and flattening related data together, common in analytics and columnar data warehouses.
Larger-than-memory processing →Larger-than-memory processing is a query engine's ability to correctly and efficiently process datasets or intermediate results that exceed the amount of RAM available, typically by spilling data to disk.
FAQS
A CTE (WITH ... AS (...)) only exists for the duration of the single query it's defined in and can't be reused across separate statements. A temporary table persists across multiple statements within a session, letting you build on intermediate results step by step, and is automatically dropped when the session ends.
DuckDB temporary tables are always held in memory, even when the main database is a persistent on-disk file, and they are dropped automatically when the connection is closed.
