Temporary tables are useful when you need to store data only for a short time while running SQL statements. Instead of writing one very large query, you can break the work into steps and store intermediate results in a temporary table.
PostgreSQL and Oracle both support temporary tables, but they implement them differently. This post explains how temporary tables work in both systems and highlights the main differences between them.
What Is a Temporary Table?
A temporary table stores data that is only needed for a limited time, typically during a session or a transaction. Typical situations include saving intermediate query results, preparing data before inserting it into other tables, simplifying complex SQL statements, or staging data during imports or batch jobs.
Temporary tables behave like normal tables in many ways. You can insert, update, and query them using standard SQL. The main difference is that their lifetime is limited and they are automatically cleaned up later.
Temporary Tables in PostgreSQL
In PostgreSQL, the table itself is temporary. The table is created during a session and automatically removed when the session ends.
This means temporary tables are usually created inside scripts, functions, or interactive sessions whenever they are needed. There is no need to define them permanently in the database schema.
Here’s how to create a temporary table:
CREATE TEMP TABLE temp_orders (
id INT,
amount NUMERIC
);
You can use the table like any normal table.
INSERT INTO temp_orders VALUES (1, 100);
SELECT * FROM temp_orders;
When the session ends, the table is automatically dropped. Another useful property is that different sessions can create temporary tables with the same name without interfering with each other.
Transaction Options
PostgreSQL also supports an ON COMMIT clause that controls what happens when a transaction commits.
Example:
CREATE TEMP TABLE temp_orders (
id INT
) ON COMMIT DELETE ROWS;
Possible options are ON COMMIT PRESERVE ROWS (the default), ON COMMIT DELETE ROWS, and ON COMMIT DROP.
Example:
CREATE TEMP TABLE temp_orders (
id INT
) ON COMMIT DROP;
After the commit, the table itself is removed.
Temporary Tables in Oracle
Oracle implements temporary tables differently. Oracle provides Global Temporary Tables. The key idea is that the table definition is permanent, but the data stored in the table is temporary. Usually the table is created once as part of the database schema and reused by applications.
Example:
CREATE GLOBAL TEMPORARY TABLE temp_orders (
id NUMBER,
amount NUMBER
)
ON COMMIT DELETE ROWS;
Like PostgreSQL, Oracle also supports ON COMMIT clauses. They determine when the rows disappear. However, Oracle does not support ON COMMIT DROP.
Example: Working with Intermediate Results
Temporary tables are often used to store intermediate query results. For example, suppose you want to find customers with the highest total spending.
First step:
CREATE TEMP TABLE customer_totals AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
Second step:
SELECT *
FROM customer_totals
ORDER BY total_spent DESC
LIMIT 10;
Splitting a task into multiple steps like this can make queries easier to read and maintain.
Why PostgreSQL Temporary Tables Are Often More Practical
The PostgreSQL approach is often more flexible in everyday work. Because the table itself is temporary, it can be created exactly when it is needed and disappears automatically afterwards. This keeps the database schema cleaner because temporary helper tables do not remain in the system permanently.
This model also works well for scripts, reporting tasks, and data processing jobs. A script can create the temporary tables it needs, perform several processing steps, and then end the session. When the session ends, the database removes the tables automatically without any extra cleanup.
In contrast, Oracle requires the temporary table structure to exist permanently in the schema. Even though the stored data is temporary, the table itself remains part of the database design. Over time this can lead to many helper tables that exist only to support certain procedures or batch jobs.
Another advantage of the PostgreSQL approach is flexibility. Developers can define temporary tables with different structures whenever they are needed, without changing the permanent schema. This can make development, testing, and data exploration much easier.
For these reasons, I find the PostgreSQL model more natural and convenient, especially for ad-hoc queries, data analysis, and multi-step data processing tasks.
Schneide Blog 