Temporary Table

A temporary table in PostgreSQL is a session-scoped table that is automatically dropped when the database session that created it ends. Temporary tables are private to their session — other concurrent sessions cannot see or access them — making them ideal for storing intermediate results without polluting the permanent schema.

Creating and using temporary tables

TEMP and TEMPORARY are interchangeable keywords:

CREATE TEMP TABLE mytemp (id INT);

INSERT INTO mytemp (id) VALUES (1), (2), (3)
RETURNING *;

-- Output:
--  id
-- ----
--   1
--   2
--   3
-- (3 rows)

If a second database session tries to query the same table, it gets an error because the table does not exist in that session:

-- In a second session:
SELECT * FROM mytemp;
-- ERROR:  relation "mytemp" does not exist

To drop a temporary table before the session ends, use DROP TABLE — there is no DROP TEMP TABLE variant:

DROP TABLE mytemp;

Temporary tables and name conflicts

A temporary table can share the same name as a permanent table, though this is not recommended. When a naming conflict exists, the temporary table takes precedence and shadows the permanent table for the duration of the session:

CREATE TABLE customers (id SERIAL PRIMARY KEY, name VARCHAR NOT NULL);

CREATE TEMP TABLE customers (customer_id INT);

-- This query now hits the temporary table, not the permanent one:
SELECT * FROM customers;
-- Output:
--  customer_id
-- -------------
-- (0 rows)

PostgreSQL stores temporary tables in a special schema (e.g., pg_temp_3). You cannot specify the schema in the CREATE TEMP TABLE statement. To access the permanent table while the temp table exists, qualify it with its schema:

SELECT * FROM public.customers;

When to use temporary tables

• Intermediate storage — break complex queries into steps, storing partial results in a temp table to simplify logic and allow indexing between steps.
• Session isolation — multiple sessions can use the same temp table name without conflict, so each gets its own isolated copy of the data.
• Transaction scope — use ON COMMIT DROP to make the temp table visible only within a single transaction: CREATE TEMP TABLE t (...) ON COMMIT DROP;
• ETL staging — load and transform raw data in a temp table before inserting the cleaned rows into a permanent table.