Temporary Tables

Learning Focus

Use this lesson to understand Temporary Tables with practical syntax and examples.

Concept Overview

Temporary tables are session-scoped tables that you can use to stage intermediate results across multiple statements.

In PostgreSQL:

• You create them with CREATE TEMP TABLE (or CREATE TEMPORARY TABLE).
• They live in a special schema (pg_temp) and are visible only to your session.
• They are dropped automatically at session end, or earlier depending on ON COMMIT behavior.

Why is it important?

• Multi-step workflows: stage results for repeated reuse across multiple queries
• Debugging: inspect intermediate states while developing complex transformations
• Performance: materialize a large intermediate set once, index it, then join repeatedly

Where does it fit?

Temporary tables are an alternative to CTEs when you need reuse across multiple statements. They also complement transactions and batch ETL jobs.

---

Syntax & Rules

Core Syntax

CREATE TEMP TABLE tmp_name (
  ...
);

CREATE TEMP TABLE tmp_name AS
SELECT ...;

Available Options / Parameters

Option	Syntax	Meaning
Temp table	CREATE TEMP TABLE ...	session-scoped table
ON COMMIT PRESERVE ROWS	default	keep rows after commit
ON COMMIT DELETE ROWS	... ON COMMIT DELETE ROWS	truncate temp table at commit
ON COMMIT DROP	... ON COMMIT DROP	drop temp table at commit
Indexing	CREATE INDEX ...	speed joins/filters on temp data

Key Rules and Considerations

• Temp tables are per-session; other sessions cannot see them.
• Temp tables can shadow a permanent table name for your session (be careful).
• If the planner needs good row estimates, run ANALYZE on a large temp table.
• Prefer temp tables when you need multi-statement reuse; prefer CTEs for single-statement readability.

---

Step-by-Step Examples

Example 1: Stage a Paid-Orders Subset (Beginner)

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO orders (customer_id, status, amount) VALUES
  (1, 'paid', 10.00),
  (1, 'paid', 25.00),
  (2, 'failed', 5.00),
  (2, 'paid', 50.00);

CREATE TEMP TABLE tmp_paid_orders AS
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'paid';

SELECT customer_id, SUM(amount) AS revenue
FROM tmp_paid_orders
GROUP BY customer_id
ORDER BY customer_id;

Expected output:

 customer_id | revenue
-------------+---------
 1           | 35.00
 2           | 50.00
(2 rows)

Explanation:

• The temp table persists for the session, so you can run multiple queries against it.

---

Example 2: ON COMMIT DROP for Automatic Cleanup (Intermediate)

BEGIN;

CREATE TEMP TABLE tmp_customer_revenue ON COMMIT DROP AS
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

SELECT *
FROM tmp_customer_revenue
ORDER BY revenue DESC;

COMMIT;

Expected output (inside the transaction):

 customer_id | revenue
-------------+---------
 2           | 50.00
 1           | 35.00
(2 rows)

Explanation:

• ON COMMIT DROP removes the temp table when the transaction commits.

---

Example 3: Index a Temp Table for Heavy Joins (Advanced)

CREATE TABLE customers (
  customer_id bigint PRIMARY KEY,
  full_name   text NOT NULL
);

INSERT INTO customers (customer_id, full_name) VALUES
  (1, 'Alice'),
  (2, 'Bob');

-- Temp table reused in multiple joins
CREATE TEMP TABLE tmp_paid_orders2 AS
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'paid';

CREATE INDEX tmp_paid_orders2_customer_id_idx ON tmp_paid_orders2 (customer_id);
ANALYZE tmp_paid_orders2;

SELECT c.full_name, SUM(t.amount) AS revenue
FROM customers c
JOIN tmp_paid_orders2 t ON t.customer_id = c.customer_id
GROUP BY c.full_name
ORDER BY revenue DESC;

Expected output:

 full_name | revenue
-----------+---------
 Bob       | 50.00
 Alice     | 35.00
(2 rows)

Explanation:

• Indexes on temp tables can matter when the temp table is large or used repeatedly.

---

Example 4: Beware Name Shadowing (Advanced)

If you create a temp table with the same name as a permanent table, your session will resolve the temp table first.

-- If a permanent table "reports" exists, this temp table will shadow it for your session.
CREATE TEMP TABLE reports (id integer);

Expected outcome:

• Queries like SELECT * FROM reports; will reference the temp table in your session.

---

Practical Use Cases

1) ETL staging inside a session

CREATE TEMP TABLE tmp_cleaned AS
SELECT * FROM raw_import WHERE is_valid;

2) Reusing an expensive intermediate set

CREATE TEMP TABLE tmp_candidates AS
SELECT ...
FROM huge_table
WHERE ...;

3) Debugging transformations

SELECT * FROM tmp_candidates LIMIT 50;

4) Multi-step dedup with inspection

CREATE TEMP TABLE tmp_dupes AS
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

5) Performance experiments

CREATE TEMP TABLE tmp AS SELECT ...;
EXPLAIN SELECT ... FROM tmp JOIN ...;

---

Common Mistakes & Troubleshooting

1) Expecting temp tables across sessions

Bad outcome:

• Another connection cannot see your temp table.

Fix:

• Create temp tables per session or use permanent staging tables.

---

2) Forgetting cleanup in long-lived sessions

Bad outcome:

• Many temp tables accumulate and waste resources.

Fix:

DROP TABLE IF EXISTS tmp_paid_orders;

Or use:

CREATE TEMP TABLE tmp_x (...) ON COMMIT DROP;

---

3) Slow joins because the temp table has no index

Bad outcome:

• Large hash joins/scans on temp data.

Fix:

CREATE INDEX tmp_x_key_idx ON tmp_x (key);
ANALYZE tmp_x;

---

4) Using temp tables where a CTE is enough

Bad outcome:

• Extra statements and cleanup complexity.

Fix:

• If you only need the intermediate once, prefer a CTE in a single statement.

---

Debugging checklist:

1. Confirm whether you need multi-statement reuse (temp table) or one statement (CTE).
2. Check ON COMMIT behavior if the table seems to disappear.
3. If performance is slow, add indexes and run ANALYZE.
4. Avoid name collisions with permanent tables.
5. Drop temp tables explicitly in long sessions.

---

Best Practices

• ✅ Use temp tables for large intermediates reused across multiple statements. ❌ Avoid temp tables for one-off transforms.
• ✅ Use ON COMMIT DROP when the temp table is only needed inside one transaction. ❌ Avoid leaving temp tables around unintentionally.
• ✅ Index and ANALYZE temp tables when they become join inputs. ❌ Avoid assuming planner estimates are good without statistics.
• ✅ Use clear naming (tmp_*, stg_*) for temp objects. ❌ Avoid shadowing real table names.
• ✅ Clean up in scripts (DROP TABLE IF EXISTS ...). ❌ Avoid relying on session end for cleanup in long-lived app connections.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL
);

INSERT INTO practice_orders (customer_id, status, amount) VALUES
  (1, 'paid', 10.00),
  (1, 'failed', 5.00),
  (2, 'paid', 20.00);

Exercise 1 (Easy): Create a temp table from a query

Task: Create tmp_paid containing only paid orders.

-- Your SQL here

Solution:

CREATE TEMP TABLE tmp_paid AS
SELECT order_id, customer_id, amount
FROM practice_orders
WHERE status = 'paid';

---

Exercise 2 (Medium): Aggregate from the temp table

Task: Compute revenue per customer from tmp_paid.

-- Your SQL here

Solution:

SELECT customer_id, SUM(amount) AS revenue
FROM tmp_paid
GROUP BY customer_id
ORDER BY customer_id;

---

Exercise 3 (Advanced): Use ON COMMIT DROP

Task: Create a temp table that drops at commit.

-- Your SQL here

Solution:

BEGIN;
CREATE TEMP TABLE tmp_paid2 ON COMMIT DROP AS
SELECT * FROM practice_orders WHERE status = 'paid';
SELECT COUNT(*) FROM tmp_paid2;
COMMIT;

---

Connection to Other Concepts

Concept	Why it matters
Common Table Expressions (CTEs)	alternative for single-statement staging
Transactions	temp table lifetime and ON COMMIT behavior
Indexes	temp table indexes can be critical for joins
EXPLAIN	validate plans when staging intermediates
Window functions	often applied to staged data for ranking

---

Visual Learning Diagram

flowchart TD
  A[Base Tables] --> B[CREATE TEMP TABLE]
  B --> C[Multiple Queries Reuse]
  C --> D[Index + ANALYZE]
  B --> E[ON COMMIT DROP]
  E --> F[Auto Cleanup]
  B --> G[Session Scoped]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Expecting cross-session visibility	missing table errors	remember temp tables are session-scoped
No cleanup in long sessions	resource growth	drop explicitly or use ON COMMIT DROP
No index/statistics on large temp tables	slow joins	add indexes and run ANALYZE
Shadowing a permanent table name	confusing behavior	use tmp_ prefixes
Using temp tables for one-off logic	extra complexity	use a CTE instead

---

Quick Reference

CREATE TEMP TABLE t (...);
CREATE TEMP TABLE t AS SELECT ...;
CREATE TEMP TABLE t (...) ON COMMIT DROP;
CREATE INDEX t_key_idx ON t (key);
ANALYZE t;

What's Next

• Previous: Recursive CTE Patterns - Review the previous lesson to reinforce context.
• Next: Window Functions - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.