Working with Temporary Tables in PostgreSQL

Temporary tables are tables that exist only within the current session or transaction. As soon as your session ends (or the transaction is closed), the temporary table and its data vanish like footprints in the sand. They're perfect for short-term data storage, intermediate calculations, or prepping data for complex operations.

Why do you need them?

• Temporary data storage: For example, if you have some heavy calculations on data that need to be done in several steps. Instead of overloading your main table, you can use temporary tables.
• Data analysis: You can gather data from several sources, process it, and then drop the temporary table after your analysis is done.
• Optimizing complex queries: Sometimes it's better to split a query into several steps using temporary tables to speed things up.
• Minimizing risks: There's no risk of accidentally messing up your real tables — temporary tables are totally isolated.

The magic of temporary tables is that their data is visible only to you! Other database users can't peek into them, which makes temporary tables super safe for experiments.

Creating Temporary Tables

Creating a temporary table is pretty much like creating a regular table, you just add the TEMP or TEMPORARY keyword.

Syntax:

CREATE TEMP TABLE table_name (
    column_name data_type constraints,
    ...
);

Easy, right? The temporary table will disappear automatically when your session ends.

For example, let's create a temporary table to store student data:

CREATE TEMP TABLE temp_students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 0)
);

Now we've got a temporary table temp_students where you can insert data and work with it just like a regular table. Just don't forget — it'll disappear as soon as you close your session.

Creating a Temporary Table from a Query

Sometimes you don't want to describe the structure of a temporary table by hand — especially if you just want to save the result of some query and work with it. In this case, you can use:

CREATE TEMP TABLE table_name AS
SELECT ...;

This construction not only creates a temporary table, but immediately fills it with data from the query.

Let's say you want to save students who aren't enrolled in any course:

CREATE TEMP TABLE temp_unregistered_students AS
    SELECT s.id, s.name
    FROM students s
    LEFT JOIN enrollments e ON s.id = e.student_id
    WHERE e.student_id IS NULL;

Now you have a temp_unregistered_students table you can use in other parts of your script — no need to repeat the query.

Why do this?

• No need to explicitly specify columns and types — PostgreSQL will figure them out from the SELECT result.
• You can reuse temporary data multiple times without recalculating.
• This is especially handy in reports, ETL operations, and analytics.

Temporary Tables Inside Transactions

You can create temporary tables inside transactions. Important: the default behaviour of a TEMP TABLE is ON COMMIT PRESERVE ROWS — the table lives until the end of the session, not until COMMIT. To actually drop the table on COMMIT, spell that out with ON COMMIT DROP:

Example:

BEGIN;

CREATE TEMP TABLE temp_transactions (
    transaction_id SERIAL PRIMARY KEY,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50)
) ON COMMIT DROP;

INSERT INTO temp_transactions (amount, status)
VALUES (100.50, 'Pending');

-- Let's check out the data
SELECT * FROM temp_transactions;

COMMIT;
-- Now temp_transactions really is gone (because we asked for ON COMMIT DROP)

The three ON COMMIT options are:

• PRESERVE ROWS — the default, do nothing on commit (table is dropped only when the session ends).
• DELETE ROWS — the table is TRUNCATEd at COMMIT but stays around.
• DROP — the table is dropped at COMMIT.

If you run ROLLBACK instead of COMMIT, the table created inside the transaction is removed regardless of the ON COMMIT option (the whole CREATE TEMP TABLE is rolled back).

Using Temporary Tables in Real Tasks

Example 1: Temporary Data Join

Let's say we have two tables: students and courses. We need to figure out which students aren't enrolled in any courses yet. We can first gather the data in a temporary table, then analyze it.

CREATE TEMP TABLE temp_unregistered_students AS
	SELECT s.id, s.name
	FROM students s
	LEFT JOIN enrollments e ON s.id = e.student_id
	WHERE e.student_id IS NULL;

-- Now we can work with the temp_unregistered_students temporary table.
SELECT * FROM temp_unregistered_students;

Example 2: Prepping Data for a Report

Sometimes you need to gather data from several tables, transform it, and prep a report. Temporary tables are perfect for this.

CREATE TEMP TABLE temp_sales AS
	SELECT p.product_id, p.name, SUM(s.quantity) AS total_quantity
	FROM products p
	JOIN sales s ON p.product_id = s.product_id
	GROUP BY p.product_id, p.name;

-- We've created a temporary table with the result. Now you can build a report on top of it:
SELECT name, total_quantity FROM temp_sales WHERE total_quantity > 50;

Tricks and Features of Temporary Tables

Naming temporary tables: if you already have a regular table named students, you can still create a temporary table with the same name! The temporary table will take priority in your session. But this can get confusing if you forget you're working with a temporary table.

Optimizing temporary tables: PostgreSQL automatically creates indexes for columns with PRIMARY KEY or UNIQUE constraints even in temporary tables. If you need to speed up working with data, you can manually add indexes:

CREATE INDEX idx_temp_students_age ON temp_students (age);

Dropping a temporary table: if you want to drop a temporary table before your session ends, use the DROP TABLE command:

DROP TABLE temp_students;

Data volume in temporary tables: physically, temporary tables are stored the same way regular tables are — in PostgreSQL files (inside a special temp tablespace). What makes them fast is the session-local temp_buffers cache (8 MB by default), so small TEMP TABLEs operate mostly in memory. Large temp tables spill to disk just like regular tables.

Common Mistakes and How to Avoid Them

Mistake 1: "Table already exists"

If you try to create a temporary table with a name that's already taken by another temporary table, you'll get an error. Use CREATE TEMP TABLE IF NOT EXISTS or drop the table before creating it:

DROP TABLE IF EXISTS temp_students;
CREATE TEMP TABLE temp_students (...);

Mistake 2: "Table disappears too soon"

If you create a temporary table inside a transaction, don't forget it'll disappear when the transaction ends. Plan your actions ahead!