Temp Tables and CREATE TABLE AS SELECT in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Common Table Expressions (CTEs)
What is CREATE TABLE AS in SQL?
CREATE TEMP TABLE ... AS SELECT runs a query and stores the result as a table you can reference repeatedly in the same session.
A CTE names a subquery within a single statement. The moment that statement finishes, the result is gone. A temp table is different: the result persists for the rest of your session, and you can query it as many times as you like with separate SELECT statements — without re-running the original computation each time.
How do you create a temp table from a query?
You're building a multi-step analysis. Step one is an expensive aggregation across a large orders table. Steps two and three both need that aggregated data. With a CTE, you'd have to include the aggregation inside every query that needs it. With a temp table, you run it once and store the result:
CREATE TEMP TABLE monthly_revenue AS
SELECT
DATE_TRUNC('month', ordered_at) AS month,
status,
SUM(total_amount) AS revenue
FROM orders
GROUP BY 1, 2;Now monthly_revenue is a real table in your session. Query it as many times as you need:
SELECT status, AVG(revenue) FROM monthly_revenue GROUP BY status;
SELECT month, SUM(revenue) FROM monthly_revenue GROUP BY month;The aggregation ran once. Both queries read from the stored result.
How long does a temp table last in PostgreSQL?
When your session ends, PostgreSQL drops the temp table automatically. You don't need to clean it up. And the table is invisible to other sessions — even if two analysts create a temp table with the same name, each gets a private copy in their own session. There is no naming conflict.
You can also add indexes to a temp table after creating it, which a CTE cannot have. For large intermediate results that get queried repeatedly with WHERE filters, an index can meaningfully speed up the downstream queries.
Should you use a temp table or a CTE?
When should you use a CTE instead? When the intermediate result is only needed once, inside a single query. When you need it across multiple queries or statements in the same session, a temp table is the right tool.
What happens to a temp table when a transaction rolls back?
The one thing that trips people up: a temp table created inside a transaction is dropped if the transaction rolls back.
If your session runs BEGIN, creates a temp table, then hits an error that triggers a ROLLBACK, the temp table disappears along with everything else in that transaction. If you're building a multi-step pipeline with temp tables, be aware of your transaction boundaries.
You create a temp table in one session. Can a second concurrent session query that same temp table?
Practice CREATE TABLE AS in SQL
Brightlane's reporting pipeline materializes an order-status summary into a temp table to avoid rerunning the aggregation for every downstream report. The query that populates the temp table needs to return the order count and combined order amount for each status value.
Write a query to return the status, order count, and total amount for each status value.
Assumptions:
- The
orderstable has one row per order with astatusand atotal_amount. - Each unique
statusvalue should appear once in the result. - For each status, the order count is the number of orders carrying that
status. The total amount is the combinedtotal_amountacross those orders.
Output:
- One row per status, with columns
status,order_count, andstatus_total.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 CREATE TABLE AS practice problems
Write a query to return the status, order count, and total amount for each status value.
Write a query to return the category ID, product count, and average price for each category_id value.
Write a query to return the department ID, employee count, and earliest hire date for each department_id value.
Write a query to return each high-activity customer's ID and order count.
Write a query to return each qualifying customer's ID, order count, and total spend.
Write a query to return each customer's ID, order count, total spend, and the average individual order amount across every order.
Write a query to return the status and order count for each high-volume status.
Write a query to return each qualifying category's ID, product count, combined price, and average product price.
Write a query to return each qualifying customer's ID and total spend.
Start learning to practice all 9 CREATE TABLE AS problems, with instant grading and mastery tracking.
Common questions about CREATE TABLE AS
Do you have to drop a temp table when you finish?
No. PostgreSQL removes it when the session ends, so cleanup is automatic. Dropping it yourself is still useful mid-session when you want to rebuild it with a different shape, because creating over an existing name fails.
Can you index a temp table?
Yes, which is one of the things a CTE cannot offer. When a large intermediate result is queried repeatedly with the same filter, an index on that column can make the difference between a quick answer and a slow one.
Is a temp table visible to the rest of the session?
Yes, to every later statement until the session ends, which is exactly what separates it from a CTE. The result is computed once and read as many times as you like, instead of being rebuilt inside each query that needs it.
