Postgres Temporary Tables: A Guide to Data Manipulation

Table of Contents

• Introduction to Temporary Tables in PostgreSQL
• Creating Temporary Tables
  • Example: Creating a Temporary Table
• Inserting Data into Temporary Tables
  • Example: Inserting Data into a Temporary Table
• Querying Temporary Tables
  • Example: Querying Data from a Temporary Table
• Dropping Temporary Tables
  • Example: Dropping a Temporary Table
• Scope of Temporary Tables
• Example: Using Temporary Tables for Data Transformation
• Conclusion
• References

Introduction to Temporary Tables in PostgreSQL

When working with databases, there are situations where you need to store temporary data that is only needed for the duration of a session or a specific transaction. Postgres (PostgreSQL) provides a powerful feature called temporary tables to handle such scenarios. In this blog post, we will explore temporary tables in Postgres, understand their benefits, and provide multiple code examples to illustrate their usage.

Understanding Temporary Tables

A temporary table in Postgres is a table whose definition and data are visible only within the current session or transaction. These tables are created and managed in a similar way to regular tables, but they are automatically dropped at the end of the session or transaction, depending on their scope.

Temporary tables can be useful in various scenarios, including:

• Storing intermediate results during complex queries or data transformations.
• Breaking down a complex task into smaller, manageable steps.
• Caching data for faster access within a session.
• Isolating data for different concurrent sessions or transactions.

It is worth noting that a temporary table is very different from a view. A view is a virtual table that is based on the result of a SELECT statement. Views do not store data themselves, but instead, they retrieve data from the underlying tables every time they are queried. Views can be used to simplify complex queries, provide an abstraction layer over the underlying tables, and restrict access to certain columns or rows.

The main difference between temporary tables and views is that temporary tables store data, while views do not. Temporary tables can be used to store intermediate results for complex queries, while views provide an abstraction layer over the underlying tables and can be used to simplify complex queries or restrict access to certain data.

Now let's explore how to create and use temporary tables in Postgres.

Creating Temporary Tables

Temporary tables are created using the CREATE TEMPORARY TABLE statement. The syntax is as follows:

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

-- TEMPORARY can be shorthanded to TEMP

CREATE TEMP TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

Imagine you were doing (or needed to do) analysis on your sales, you might want to store temporary data for analysis within a session and to do so, you decide to create a temporary table named sales_analysis with columns product_name and sales_amount.

-- Create a temporary table
raiden=# CREATE TEMPORARY TABLE sales_analysis(
    product_name VARCHAR(50),
    sales_amount NUMERIC(10, 2)
);

Temporary tables follow the same rules as regular tables in terms of column definitions, constraints, and indexes. You can specify primary keys, unique constraints, and even create indexes on temporary tables to optimize data retrieval.

Inserting Data into Temporary Tables

Once you have created a temporary table, you can insert data into it using the INSERT INTO statement. We'll just use dummy data for this example

-- Insert data into the temporary table
raiden=# INSERT INTO sales_analysis (product_name, sales_amount)
VALUES ('Product A', 100.50), ('Product B', 75.20), ('Product C', 150.00);

Verify that the data has been added

INSERT 0 3

Querying Temporary Tables

Temporary tables can be queried in the fashion as regular tables. You can use the SELECT statement to retrieve data from the temporary table

-- Retrieve data from the temporary table
raiden=# SELECT * FROM sales_analysis;

raiden=# SELECT * FROM sales_analysis;
 product_name | sales_amount
--------------+--------------
 Product A    |       100.50
 Product B    |        75.20
 Product C    |       150.00
(3 rows)

You can apply various filtering, sorting, and aggregation operations on temporary tables, just like you would with regular tables. Let's use SUM in this instance.

raiden=# SELECT SUM(sales_amount) as total_sales FROM sales_analysis;

 total_sales
-------------
      325.70
(1 row)

Temporary tables provide a flexible and efficient way to manipulate and analyze data within a session.

Dropping Temporary Tables

Temporary tables are automatically dropped at the end of the session or transaction, depending on their scope. However, you can explicitly drop a temporary table before the end of the session using the DROP TABLE statement.

-- Drop the temporary table
raiden=# DROP TABLE sales_analysis;

raiden=# SELECT * FROM sales_analysis;
ERROR:  relation "sales_analysis" does not exist
LINE 1: SELECT * FROM sales_analysis;

As you see, dropping a temporary table will remove all its data, and you won't be able to access it afterward. Therefore, make sure to drop temporary tables only when you no longer need their data.

Scope of Temporary Tables

Temporary tables in Postgres can have different scopes, depending on where they are created. There are three types of temporary table scopes:

1. Session Temporary Tables: These tables are visible only within the current session and are automatically dropped at the end of the session or when the session is terminated.

2. Transaction Temporary Tables: These tables are visible only within the current transaction and are automatically dropped at the end of the transaction or when the transaction is rolled back.

3. Immediate Temporary Tables: These tables are visible within the current session and all subsequent sessions until the table is explicitly dropped or the session ends.

The scope of a temporary table depends on the context in which it is created. To create a session temporary table explicitly, you can use the CREATE TEMPORARY TABLE statement.

Transaction temporary tables are created using the same statement within a transaction block. Immediate temporary tables are created using the CREATE TEMPORARY TABLE statement outside of a transaction block.

Example: Using Temporary Tables for Data Transformation

Suppose we have fast food restaurant and we have our orders table with columns order_id, product_id, and quantity. We want to calculate the total sales for each product by summing the quantities sold. We can use a temporary table to store the intermediate results and simplify the query.

-- Create a temporary table to store intermediate results
raiden=# CREATE TEMPORARY TABLE temp_product_sales (
    product_id INT,
    total_sales INT
);

-- Insert data into the temporary table
raiden=# INSERT INTO temp_product_sales (product_id, total_sales)
SELECT product_id, SUM(quantity) AS total_sales
FROM orders
GROUP BY product_id;

-- Retrieve the total sales for each product
raiden=# SELECT p.product_name, t.total_sales
FROM temp_product_sales t
JOIN products p ON p.product_id = t.product_id;

In the above example, we create a temporary table temp_product_sales to store the intermediate results of the total sales calculation. We insert the aggregated data using a SELECT statement with a GROUP BY clause.

We join the temporary table with the products table to retrieve the product names along with the total sales.

Temporary tables make improving the readability and maintainability of our queries.

Conclusion

Temporary tables in PostgreSQL can help you manage and manipulate data more efficiently by storing intermediate results for complex queries, performing operations on a subset of data without affecting the original data, and working with data in a way that is isolated from other sessions.

Temporary tables are automatically dropped at the end of a session or transaction, without the need of manual cleanup, allowing for a more streamlined workflow

References

https://www.postgresql.org/docs/current/sql-createtable.html
https://dba.stackexchange.com/questions/239060/why-cant-temporary-tables-reference-regular-non-temp-tables-in-postgresql