SQL TEMPORARY

What is the SQL TEMPORARY keyword?

TEMPORARY marks a table, view, or other object so it lives only for the current session or transaction and is dropped automatically.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TEMPORARY: PostgreSQL, MySQL, MariaDB, Oracle (GLOBAL TEMPORARY), SQLite, SQL Server (uses #local or ##global temp tables), Snowflake (TRANSIENT & TEMP).

SQL TEMPORARY Full Explanation

TEMPORARY (or its shorthand TEMP) is a modifier placed in front of CREATE TABLE, CREATE VIEW, and in some systems CREATE SEQUENCE or INDEX. A temporary object is stored in a separate internal schema and is visible only to the session that created it. The object is removed implicitly when the session ends, or earlier if the database supports transaction-scoped lifetimes via ON COMMIT clauses.Key points:- Scope: Visible only inside the connection that created it. Other sessions cannot read or write to the object, even if they use the same name.- Lifetime: Dropped automatically at session end. PostgreSQL allows ON COMMIT PRESERVE ROWS, DELETE ROWS, or DROP to control earlier cleanup. MySQL always drops on session close.- Persistence: Not logged in the main catalog, so temporary objects do not survive server restarts or failovers.- Permissions: Users need CREATE TEMPORARY TABLE or general CREATE rights. Temp objects bypass some naming or foreign-key checks because they reside in an isolated namespace.- Performance: Useful for staging intermediate results, breaking complex queries into steps, or caching lookups without polluting permanent schemas.Caveats:- Name collisions: A temp table can mask a permanent table of the same name inside the session, potentially leading to confusion.- Storage limits: Large temp tables can consume disk on temp tablespaces and may affect other sessions.- Unsupported features: Some engines disallow foreign keys, triggers, or indexes on temp objects, or implement them differently.

SQL TEMPORARY Syntax

-- Generic SQL standard
CREATE TEMPORARY TABLE table_name (
    column_definition [, ...]
);

-- PostgreSQL variant with transaction scope
CREATE TEMP TABLE table_name (
    id INT PRIMARY KEY,
    val TEXT
) ON COMMIT DROP;

SQL TEMPORARY Parameters

Example Queries Using SQL TEMPORARY

-- 1. Build a 7-day sales snapshot
CREATE TEMPORARY TABLE temp_sales AS
SELECT *
FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days';

-- 2. Aggregate inside the session
SELECT customer_id, SUM(amount) AS weekly_total
FROM temp_sales
GROUP BY customer_id;

-- 3. PostgreSQL transaction-scoped temp table
BEGIN;
CREATE TEMP TABLE temp_ids(id INT) ON COMMIT DELETE ROWS;
INSERT INTO temp_ids VALUES (1),(2),(3);
COMMIT; -- rows are now gone, table persists until session ends

Expected Output Using SQL TEMPORARY

Each CREATE TEMPORARY TABLE statement creates an isolated in-memory or on-disk table available only to the current session
Queries against it return normal result sets
When the session disconnects (or earlier, per ON COMMIT), the database silently drops the table and frees resources

Use Cases with SQL TEMPORARY

Staging intermediate results in ETL pipelines
Breaking complex reporting queries into simpler steps
Caching small lookup tables for repeated joins
Preventing name pollution in shared development databases
Safely testing DDL without affecting production schemas

Common Mistakes with SQL TEMPORARY

Assuming the temp table persists after disconnecting
Forgetting that a temp table can hide a permanent table of the same name
Attempting to grant privileges on a temp table to other users
Exceeding temp tablespace limits and causing errors
Relying on engine-specific behavior (e.g., MySQL allows temp table names longer than permanent limits)

First Introduced In

SQL:1999

Frequently Asked Questions

What types of objects can use TEMPORARY?

Primarily tables. PostgreSQL also allows TEMPORARY views, sequences, and indexes. Other engines restrict it to tables only.

How long does a TEMPORARY table last?

It lasts for the lifetime of the session unless the engine supports transaction-scoped temp tables. In PostgreSQL you can control this with ON COMMIT.

Can a TEMPORARY table have the same name as a permanent table?

Yes. Inside the session the temp table masks the permanent one. Outside the session the permanent table is visible again.

Do I need to drop a TEMPORARY table manually?

Not usually. The database cleans it up automatically, but you can drop it explicitly with DROP TABLE if required for resource management.