Temporary Tables

Sometimes a query is complex enough that you want to break it into stages, saving intermediate results along the way instead of nesting subqueries ten levels deep. A temporary table gives you a real table — one you can insert into, index, and query multiple times — that automatically disappears when you are done with it.

Creating a temporary table

The syntax looks just like CREATE TABLE, with the TEMPORARY (or TEMP) keyword added:

Creating and using a temporary table

SQL

CREATE TEMPORARY TABLE high_value_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

-- Now query it like any other table
SELECT c.name, h.total_spent
FROM high_value_customers h
JOIN customers c ON c.id = h.customer_id
ORDER BY h.total_spent DESC;

You can also declare an empty temporary table with explicit column definitions, exactly as you would a permanent table:

Declaring columns explicitly

SQL

CREATE TEMPORARY TABLE staging_import (
  raw_line   TEXT,
  parsed_at  TIMESTAMP DEFAULT NOW()
);

INSERT INTO staging_import (raw_line) VALUES ('some,csv,data');

Lifetime and visibility

A temporary table is automatically dropped at the end of the session (or, depending on how it was created, at the end of the current transaction)
It is only visible to the session that created it — two different connections can each create a temporary table with the same name without conflicting
It lives in a separate namespace from permanent tables, so it won't collide with your real schema

A transaction-scoped temporary table (PostgreSQL)

SQL

CREATE TEMPORARY TABLE session_calc (id INT, score NUMERIC)
  ON COMMIT DROP;

Common use cases

Staging data during an ETL or migration step before it's cleaned and loaded into a permanent table
Breaking up a complex report into named intermediate results that are each easy to inspect and debug
Materializing an expensive calculation once so it can be joined against multiple times in the same session, instead of being recomputed

Feature	Temporary table	CTE (WITH clause)
Scope	Session or transaction	Single query only
Can be indexed?	Yes	No
Can be reused across multiple statements?	Yes	No — re-evaluated per statement
Setup overhead	Requires CREATE + INSERT/SELECT	None — inline in the query

Temp tables vs CTEs

For a single query, a common table expression (covered next in this series) is usually the simpler and preferred tool — no cleanup needed, and it reads naturally at the top of your query. Reach for a temporary table when you need to reuse a result set across several separate statements, or when the intermediate result is large enough that indexing it would meaningfully speed up later joins.

Tip

Give temporary tables clear, descriptive names just like you would any other table. It is easy to forget you are inside a multi-step script and confuse a temporary staging table with the permanent one it is feeding into.