âš¡IntermediateTemporary Objects
Temporary Tables
Create session-scoped tables that exist only for the duration of a session or transaction.
Overview
Temporary tables (TEMP or TEMPORARY) are created in a special schema and are automatically dropped when the session ends (or the transaction ends for TEMP ON COMMIT DROP). They are useful for breaking complex multi-step queries into stages, storing intermediate results, or importing data for processing.
Temporary tables are session-local different connections see different instances. Two sessions can each have a TEMP TABLE with the same name without conflict. They support all standard table features: indexes, constraints, triggers.
Key Points
- •CREATE TEMP TABLE name (...) dropped at session end
- •CREATE TEMP TABLE name ON COMMIT DELETE ROWS cleared after each transaction
- •CREATE TEMP TABLE name ON COMMIT DROP dropped after transaction
- •Session-local: different sessions have independent instances
- •Can create indexes on temp tables for faster interim queries
- •CREATE TEMP TABLE AS SELECT ... create and populate in one step
Examples
Creating and using temp tables
-- Create for the session
CREATE TEMP TABLE staging_orders (
external_id TEXT UNIQUE,
amount NUMERIC,
status TEXT
);
-- Populate and process
INSERT INTO staging_orders SELECT ... FROM import_feed;
UPDATE staging_orders SET status = 'valid' WHERE amount > 0;
INSERT INTO orders SELECT ... FROM staging_orders WHERE status = 'valid';
-- Auto-dropped when session ends
-- Create from SELECT
CREATE TEMP TABLE high_value_customers AS
SELECT id, name, SUM(total) AS lifetime FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id, customers.name
HAVING SUM(total) > 1000;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4