SQL Temporary Tables

In DuckDB, a temporary table behaves much like any other table (it stores rows, and you can join and filter it), but it is scoped to the current connection and disappears when that connection closes. Other database engines use similar ideas with different lifetime and visibility rules.

create temp table active_customers as
select customer_id, first_name, last_name, state
from customers
where is_active

TEMP and TEMPORARY are the same keyword. From there it is an ordinary table:

select a.state,
       count(*) as orders
from active_customers a
join orders o on o.customer_id = a.customer_id
group by a.state
order by orders desc, a.state

How long "temporary" actually lasts

The lifetime is the connection, not the day and not the browser tab. Open a second connection to the same database and it cannot see your temp table at all: the name does not resolve.

That is not a quirk of this site. Any client that opens a fresh connection between statements will lose connection-scoped temp tables between those statements. Whether a BI tool, notebook, or application reuses the same connection depends on the tool and its configuration.

Temp tables and temp views

Both disappear with the connection. The difference is the same as the permanent versions: a temp table stores rows, a temp view stores a query and re-runs it.

-- computed once, held as rows
create temp table customer_revenue as
select o.customer_id,
       sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id;

-- re-runs on every reference
create temp view active_customer_names as
select customer_id, first_name
from customers
where is_active;

Reach for the temp table when the step is expensive and you read it several times. Reach for the temp view when the step is cheap and you mainly wanted to give it a name.

Rebuilding one mid-script

create or replace temp table active_customers as
select customer_id, first_name
from customers
where is_active
  and state = 'PA'

CREATE OR REPLACE TEMP TABLE swaps the definition and the rows. CREATE TEMP TABLE IF NOT EXISTS does the opposite: it keeps whatever is already there and tells you nothing, which is the same trap it is on permanent tables.

The name collision to know about

A temp table can have the same name as a permanent one. When that happens the temp table wins every unqualified lookup, and schema-qualifying does not break the tie: temp objects live in a database called temp whose schema is also called main, so main.customers still finds the temp one.

-- the temp one
select * from customers;

-- still the temp one
select * from main.customers;

-- the real, permanent table
select * from memory.main.customers;

Finding them

select database_name, table_name, temporary
from duckdb_tables()
where temporary
order by table_name

When a temp table is the right answer

• An expensive step read several times. A CTE only lives for one statement, so five statements means computing it five times.
• Debugging a long query. Break it into steps, land each in a temp table, and count rows between them to find where the number goes wrong.
• Staging a load before you decide the rows are good enough to write somewhere permanent.
• Scratch work on a shared database where you should not be creating real tables at all.

And when not to: anything another person or another session needs to read. They cannot see it. That wants a real table or a view.