Temporary Tables in PostgreSQL

A temporary table in PostgreSQL is a session-scoped table that is automatically dropped when the connection that created it closes. Temporary tables are private to their session — no other connection can see or access them — making them ideal for storing intermediate results in complex data transformations without polluting the permanent schema.

Syntax

CREATE TEMP TABLE table_name (
  column_name data_type [constraint],
  ...
) [ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }];

TEMP and TEMPORARY are interchangeable. Key options:

• ON COMMIT PRESERVE ROWS — the default; rows survive the transaction, table survives the session.
• ON COMMIT DELETE ROWS — rows are truncated at each COMMIT, but the table structure persists.
• ON COMMIT DROP — the table is dropped when the transaction commits.

Practical Example

Create a staging table for an ETL pipeline within a session:

CREATE TEMP TABLE staging_events (
  raw_id       SERIAL,
  event_type   VARCHAR(50),
  payload      JSONB,
  received_at  TIMESTAMPTZ DEFAULT now()
);

INSERT INTO staging_events (event_type, payload)
VALUES
  ('purchase',  '{"product_id": 12, "amount": 49.99}'),
  ('view',      '{"product_id": 7}'),
  ('purchase',  '{"product_id": 3,  "amount": 12.50}');

-- Transform and load into the permanent table
INSERT INTO purchase_events (product_id, amount, recorded_at)
SELECT
  (payload->>'product_id')::INT,
  (payload->>'amount')::NUMERIC,
  received_at
FROM staging_events
WHERE event_type = 'purchase';

When the session ends, staging_events is dropped automatically. To drop it explicitly within the session:

DROP TABLE staging_events;

Use ON COMMIT DROP to scope the table to a single transaction:

BEGIN;

CREATE TEMP TABLE order_totals (
  order_id INT,
  total    NUMERIC(12,2)
) ON COMMIT DROP;

INSERT INTO order_totals
SELECT order_id, SUM(line_total)
FROM order_lines
GROUP BY order_id;

-- Use the data within this transaction
SELECT * FROM order_totals WHERE total > 1000;

COMMIT;
-- order_totals is automatically dropped here

Name Shadowing

A temporary table can share a name with a permanent table, but this is not recommended. The temporary table takes precedence for the duration of the session:

CREATE TABLE products (product_id SERIAL PRIMARY KEY, name VARCHAR(200));

CREATE TEMP TABLE products (product_id INT);  -- shadows the permanent table

-- This hits the temp table, not the permanent one:
SELECT * FROM products;

-- Access the permanent table explicitly:
SELECT * FROM public.products;

Testing with Vela

When building ETL pipelines that rely on temporary tables for staging, Vela database branching lets you test the full pipeline against a production-sized copy of the database — including the permanent tables that your temp tables feed into — without risking production data. Validate row counts, data quality, and transaction behavior before scheduling the pipeline against the live system.

Production Tips

• In connection-pool environments (PgBouncer, RDS Proxy), connections are reused between application requests — a temporary table created in one request may persist into the next if the session is not reset. Use explicit DROP TABLE or ON COMMIT DROP to control the lifetime precisely.
• Add indexes to temporary tables just like permanent ones — they can dramatically speed up multi-step queries within a session.
• Avoid giving temporary tables the same name as permanent tables; the shadowing behavior is a common source of subtle bugs.
• PostgreSQL stores temporary tables in a session-specific schema (e.g., pg_temp_3). You cannot explicitly specify this schema in CREATE TEMP TABLE.
• Use ON COMMIT DELETE ROWS when you want to keep the table structure across transactions but clear the data between operations (e.g., a reusable staging buffer).