SQL Temporary Tables: When and How to Use Them

Temporary tables are working surfaces. You create them, load intermediate results, query against them, and they disappear when the session ends. They fill the gap between a CTE that the optimizer may re-evaluate multiple times and a permanent table that clutters your schema.

Every major database supports temporary tables, but the syntax and behavior differ. This guide focuses on PostgreSQL with comparisons to MySQL and SQL Server where the differences matter.

Creating temporary tables

CREATE TEMPORARY TABLE temp_active_users (
  user_id   int PRIMARY KEY,
  last_seen timestamptz,
  order_count int
);

This table exists only in the current session. Other sessions cannot see it, even if they query the same name. When the session disconnects, the table is dropped automatically.

You can also create a temp table from a query:

CREATE TEMPORARY TABLE temp_high_value_orders AS
SELECT o.id, o.customer_id, o.total, o.created_at
FROM orders o
WHERE o.total > 500
  AND o.created_at > now() - interval '30 days';

This materializes the query results into a temporary table. Subsequent queries against temp_high_value_orders read from the materialized data, not from the original orders table.

Session-scoped vs transaction-scoped

By default, temporary tables live for the entire session. You can restrict them to the current transaction:

CREATE TEMPORARY TABLE temp_batch_results (
  item_id int,
  status  text
) ON COMMIT DROP;

Options for ON COMMIT:

Clause	Behavior
PRESERVE ROWS (default)	Table persists until session ends
DELETE ROWS	Table persists but rows are truncated on commit
DROP	Table is dropped when transaction commits

Transaction-scoped temp tables are useful inside stored procedures where you want guaranteed cleanup without relying on the caller to drop the table.

When temp tables beat CTEs

CTEs in PostgreSQL 12+ are often inlined by the optimizer, meaning the database may re-execute the CTE subquery every time it is referenced. This is usually fine, but there are cases where materializing the intermediate result is faster.

Use a temp table when:

1. The intermediate result is referenced multiple times. A CTE referenced in three joins may be computed three times (unless you use MATERIALIZED). A temp table is computed once.

-- CTE might be evaluated multiple times
WITH active AS (
  SELECT user_id, count(*) AS cnt
  FROM events
  WHERE created_at > now() - interval '7 days'
  GROUP BY user_id
)
SELECT a.user_id, a.cnt, u.name
FROM active a
JOIN users u ON u.id = a.user_id
WHERE a.cnt > 10;

-- Temp table: guaranteed single evaluation
CREATE TEMPORARY TABLE temp_active AS
SELECT user_id, count(*) AS cnt
FROM events
WHERE created_at > now() - interval '7 days'
GROUP BY user_id;

CREATE INDEX ON temp_active (user_id);

SELECT t.user_id, t.cnt, u.name
FROM temp_active t
JOIN users u ON u.id = t.user_id
WHERE t.cnt > 10;

2. You need an index on the intermediate result. You cannot index a CTE. A temp table can have indexes, which speeds up joins against it.

3. The query is too complex for the planner. Very long CTE chains can confuse the query planner. Breaking them into temp tables gives the planner smaller, simpler queries to optimize.

Stick with CTEs when:

• The intermediate result is small and referenced once.
• You want a single, readable query without DDL.
• You are inside a read-only transaction or connection pool that restricts DDL.

Adding indexes to temp tables

Temporary tables support indexes, constraints, and statistics just like permanent tables:

CREATE TEMPORARY TABLE temp_candidates AS
SELECT id, score, region
FROM applicants
WHERE score > 80;

CREATE INDEX ON temp_candidates (region);
CREATE INDEX ON temp_candidates (score DESC);

ANALYZE temp_candidates;

Running ANALYZE updates the planner’s statistics for the temp table, which can dramatically improve join performance if the table has more than a few thousand rows.

Unlogged tables: a persistent alternative

PostgreSQL offers UNLOGGED tables that skip WAL (Write-Ahead Log) writes. They are faster than regular tables for writes but are not crash-safe: their data is lost if the server crashes.

CREATE UNLOGGED TABLE staging_imports (
  id       serial PRIMARY KEY,
  raw_data jsonb,
  status   text DEFAULT 'pending'
);

Unlike temp tables, unlogged tables persist across sessions and are visible to all connections. They are ideal for staging areas, ETL pipelines, and any scenario where you can rebuild the data from the source.

Feature	Temp table	Unlogged table	Regular table
Visible to other sessions	No	Yes	Yes
Survives session end	No	Yes	Yes
Survives crash	No	No	Yes
WAL writes	No	No	Yes
Write speed	Fast	Fast	Normal

Temp tables in connection pools

Connection pools (PgBouncer, application-level pools) reuse sessions across requests. A temp table created by one request may still exist when the next request reuses the same connection.

Strategies to handle this:

1. Use ON COMMIT DROP inside explicit transactions.
2. Use CREATE TEMPORARY TABLE IF NOT EXISTS combined with TRUNCATE at the start of each use.
3. Use DROP TABLE IF EXISTS temp_name at the beginning of your procedure.

DROP TABLE IF EXISTS temp_work;
CREATE TEMPORARY TABLE temp_work AS
SELECT ...;

This is defensive but reliable. It prevents the “relation already exists” error that surprises developers using connection pools for the first time.

SQL Server and MySQL differences

SQL Server uses the # prefix for session temp tables and ## for global temp tables:

SELECT * INTO #temp_orders FROM orders WHERE total > 500;

SQL Server also has table variables (DECLARE @t TABLE (...)) which live in memory for small datasets but lack index support and statistics.

MySQL uses CREATE TEMPORARY TABLE with similar session-scoping semantics, but temp tables in MySQL are stored on disk by default (the MEMORY engine can be specified for in-memory storage).

Cleanup best practices

1. Drop temp tables explicitly when you are done with them. Relying on session-end cleanup is fine for interactive use but risky in long-lived application connections.
2. Name temp tables with a consistent prefix like temp_ or tmp_ so they are easy to identify in monitoring tools.
3. Keep temp tables small. If you are materializing millions of rows into a temp table, consider whether a well-indexed permanent table or a materialized view would serve better.
4. Run ANALYZE on temp tables with more than a few thousand rows before joining against them.

Temporary tables are a pragmatic tool for breaking complex queries into manageable steps, caching intermediate results, and giving the query planner smaller problems to solve. They are not a substitute for good schema design, but they fill gaps that CTEs and subqueries cannot always cover.