Temporary Tables in PostgreSQL with Examples

What is a Temporary Table in PostgreSQL?

Temporary tables in PostgreSQL are precisely what the name implies: tables that are created and exist temporarily, confined to the scope of a single database session. These tables are incredibly handy for scenarios where you need to store interim results, break down complex queries into manageable steps, or simply manage subsets of data in a session-specific manner. Unlike permanent tables, temporary tables are automatically discarded at the end of the session or when the transaction within which they were created concludes. This feature ensures that each session starts with a clean slate, devoid of any residual data from previous sessions.

Temporary tables possess the same structure and properties as regular tables but come with a few key distinctions:

1. Scope:
   Temporary tables are accessible only within the session in which they are created. Other sessions or users cannot access or modify them.
2. Lifespan:
   Temporary tables are automatically dropped at the end of the session or transaction, depending on the context in which they are used.
3. Data Persistence:
   Data stored in temporary tables is transient and does not survive beyond the scope of the session. This ensures that temporary tables do not contribute to database bloat.

Creating a PostgreSQL Temporary Table

Creating a temporary table in PostgreSQL is akin to creating a regular table, but with a few nuances.

Syntax:

The syntax for creating a temporary table in PostgreSQL is straightforward:

Syntax Explanation:

• CREATE TEMPORARY TABLE:
  This statement is used to create a temporary table. Temporary tables are only visible within the current session and are automatically dropped at the end of the session.

• table_name:
  Replace this with the desired name of your temporary table.

• (column1 datatype1, column2 datatype2, ...):
  Define the columns and their data types for your temporary table.

Example:

Let's create a simple temporary table named "temp_orders" to store order data:

Output:

Upon executing the above SQL statement, PostgreSQL will create the temporary table "temp_orders" with the specified columns. You can now use it within your session just like any other table. Any data stored in this table will only be accessible for the duration of the session.

Launching Another Session

To illustrate the session-specific nature of temporary tables, consider opening another session or a separate database connection. You'll find that the "temp_orders" table doesn't exist there. Temporary tables are confined to the session in which they were created, making them ideal for tasks that require temporary data storage without cluttering the database.

Launching another session and creating a temporary table in PostgreSQL can be accomplished using the psql command-line client. Here's a coding example that demonstrates how to do this:

Session 1: Creating a Temporary Table

In this code snippet:

• Replace your_username with your PostgreSQL username.
• Replace your_database_name with the name of the database you want to connect to.

In Session 1, we create a temporary table named temp_table, insert some data into it, and query the data.

Session 2: Launching Another Session and Accessing the Temporary Table

In Session 2, we attempt to access the temporary table temp_table created in Session 1. However, you will receive an error message indicating that the table does not exist. This illustrates that temporary tables are session-specific and are not visible or accessible in other sessions.

Launching another session is useful when you want to work in parallel with different sessions, each with its own isolated environment, and temporary tables provide a convenient way to store session-specific data.

Temporary Table Name

The name of a temporary table begins with temp_ by default, followed by a unique identifier for the session. For example, if you create a temporary table named orders, PostgreSQL will internally name it something like temp_orders_123456, where 123456 is a session-specific identifier.

Let's explore this concept with an example:

Creating a Permanent Table

Suppose you want to create a table named "employees" to store employee data, with columns for employee ID, name, and department:

In this case, you have a permanent table called "employees" that will persist in your database.

Creating a Temporary Table with the Same Name

Now, let's say you need a temporary table to perform some quick data analysis within the same session, and you decide to name it "employees" as well:

You've created a temporary table with the same name as the permanent one. This is allowed in PostgreSQL, but it's essential to understand the implications.

Accessing the Tables

When you perform a query without specifying the schema, PostgreSQL will access the temporary table by default. For example:

In this case, PostgreSQL will retrieve data from the temporary "employees" table.

Listing Tables

If you list the tables in your database, you'll notice something interesting:

The output will display both the temporary and permanent "employees" tables:

PostgreSQL has created a temporary schema (in this case, "pg_temp_1") to hold your temporary tables. You can see both the temporary and permanent tables with the same name listed here.

Accessing the Permanent Table

If you want to access the permanent "employees" table explicitly, you should use a fully qualified name, which includes the schema. For example:

This query specifies the schema ("public") and retrieves data from the permanent "employees" table, avoiding any potential conflicts with the temporary one.

Removing a Temporary Table in PostgreSQL

While PostgreSQL automatically takes care of dropping temporary tables at the end of a session, you can also explicitly remove them before the session concludes using the DROP TABLE statement:

Here's a code example that demonstrates how to create a temporary table, query it, and then remove it with the corresponding output for each command:

Output:

However, it's essential to note that attempting to drop a temporary table that either doesn't exist or has already been dropped within the session will result in an error. Thus, it's advisable to verify the existence of the table before initiating the drop operation.

Examples of Temporary Table Usage

Let's take a look at a couple of examples to illustrate the creation and usage of temporary tables in PostgreSQL.

Example 1: Creating and Using a Temporary Table

Suppose you're working with a database that stores information about employees and their projects. You want to extract the details of employees who are currently working on active projects. Here's how you could achieve this using a temporary table.

Certainly, let's create SQL code based on your scenario, populate the table, and provide the output for each SQL command.

Assuming you have two tables named employees and projects with the following structures:

Table employees:

• employee_id (INT)
• employee_name (VARCHAR)
• project_id (INT)

Table projects:

• project_id (INT)
• project_name (VARCHAR)
• status (VARCHAR)

Let's assume some example data for demonstration:

Table projects:

Table employees:

SQL code to get the details of employees who are currently working on active projects:

Output for the final SELECT statement (employee details for employees working on active projects):

In this example, a temporary table named temp_active_projects is created to hold the project IDs of active projects. The active project IDs are then inserted into the temporary table. Finally, an INNER JOIN between the employees table and the temporary table is used to retrieve employee details for those working on active projects.

Example 2: Simplifying Complex Queries

Suppose you're working with a database containing information about employees, departments, and projects. You want to identify employees who are part of departments with ongoing projects. Temporary tables can simplify this task:

Assuming you have three tables named employees, departments, and projects with the following structures:

Table employees:

• employee_id (INT)
• employee_name (VARCHAR)
• department_id (INT)

Table departments:

• department_id (INT)
• department_name (VARCHAR)

Table projects:

• project_id (INT)
• project_name (VARCHAR)
• department_id (INT)
• status (VARCHAR)

Let's assume some example data for demonstration:

Table departments:

Table projects:

Table employees:

SQL code to get employees who are part of departments with ongoing projects:

Output for the final SELECT statement (employee details for employees in departments with ongoing projects):

In this case, a temporary table named temp_ongoing_departments helps isolate departments with ongoing projects. The subsequent query effortlessly combines this information with employee details to provide a clear view of employees involved in active projects.