PostgreSQL Temporary Table

Summary: in this tutorial, you will learn about the PostgreSQL temporary table and how to manage it effectively.

Introduction to the PostgreSQL temporary tables

In PostgreSQL, a temporary table is a table that exists only during a database session. It is created and used within a single database session and is automatically dropped at the end of the session.

Creating a temporary table

To create a temporary table, you use the statement:

In this syntax:

• First, specify the name of the temporary table that you want to create after the keywords.

• Second, define a list of columns for the table.

The and keywords are equivalent so you can use them interchangeably:

The following example uses the to create a new temporary table :

Output:

If you open a second database session and query data from the table, you’ll get an error

Error:

The output indicates that the second session could not see the table.

If you terminate the current database session and attempt to query data from the table, you’ll encounter an error. This is because the temporary table was dropped when the session that created it ended.

PostgreSQL temporary table names

A temporary table can have the same name as a permanent table, even though it is not recommended.

When you create a temporary table that shares the same name as a permanent table, you cannot access the permanent table until the temporary table is removed. Consider the following example:

First, create a table named :

Second, create a temporary table with the same name:

Now, query data from the   table:

Output:

This time PostgreSQL accessed the temporary table  instead of the permanent one.

Note that PostgreSQL creates temporary tables in a special schema, therefore, you cannot specify the schema in the statement.

If you list the tables in psql, you will see the temporary table only, not the permanent one:

Output:

The output shows the schema of the temporary table is .

In this case, access to the permanent table requires qualifying the table name with its schema. For example:

Output:

Removing a PostgreSQL temporary table

To drop a temporary table, you use the statement. The following statement uses the statement to drop a temporary table:

Unlike the statement, the statement does not have the or keyword created specifically for temporary tables.

For example, the following statement drops the temporary table  that we have created in the above example:

When to use temporary tables

• Isolation of data: Since the temporary tables are session-specific, different sessions or transactions can use the same table name for temporary tables without causing a conflict. This allows you to isolate data for a specific task or session.

• Intermediate storage: Temporary tables can be useful for storing the intermediate results of a complex query. For example, you can break down a complex query into multiple simple ones and use temporary tables as the intermediate storage for storing the partial results.

• Transaction scope: Temporary tables can be also useful if you want to store intermediate results within a transaction. In this case, the temporary tables will be visible only to that transaction

Summary

• A temporary table is a short-lived table that exists during a database session or a transaction.

• Use statement to create a temporary table.

• Use the statement to drop a temporary table.