PostgreSQL – Temporary Table

A PostgreSQL temporary table is a powerful tool for managing session-specific data that only needs to exist for a short duration. Temporary tables are created within a database session or transaction and are automatically dropped when the session ends, making them ideal for intermediate data storage.

In this PostgreSQL temporary table tutorial, we’ll explore how to create and manage temporary tables effectively. This guide includes examples to help us understand how to create a temporary table in PostgreSQL and use it efficiently.

PostgreSQL Temporary Table

Temporary tables can be utilized in various scenarios, such as when executing complex queries that require multiple steps or when isolating data for specific transactions. They ensure that the data remains private to the session that created it, preventing any conflicts or visibility issues with other users or sessions.

Syntax

CREATE TEMPORARY TABLE temp_table(
   ...
);

or,

CREATE TEMP TABLE temp_table(
   ...
);

Both TEMPORARY and TEMP keywords serve the same purpose, allowing us to define a table that will exist only within the session. A temporary table is visible only to the session that creates it, remaining invisible to other sessions.

PostgreSQL Temporary Table Example

In this section, we will provide a practical example of how to create and use a temporary table in PostgreSQL. This example will demonstrate the steps involved in creating a temporary table, inserting data, and observing its session-specific behavior.

Step 1: Create a New Database

To get started, we need a dedicated environment for our operations. First, create a new database named test. This is done using the following command:

Query:

CREATE DATABASE test;

Explanation:

This command initializes a new database where we can perform our operations. It’s essential to have a separate database for testing and experimentation, ensuring that we don’t interfere with existing data.

Step 2: Create a Temporary Table

Once the database is set up, connect to it and create a temporary table named mytemp. The command to create the temporary table is:

Query:

CREATE TEMP TABLE mytemp(c INT);

Explanation:

In this step, we define a temporary table with a single column c of type INT. This table will exist only for the duration of the current session, allowing us to store integer values temporarily.

Step 3: Query the Temporary Table from Another Session

Next, let’s see the session-specific nature of temporary tables. Then, launch another session that connects to the ‘test’ database and query data from the ‘mytemp’ table:

Query:

SELECT * FROM mytemp;

Explanation:

When we run this command in a new session, PostgreSQL will return an error indicating that the mytemp table does not exist. This behavior demonstrates the isolation of temporary tables; they are not accessible from sessions that did not create them.

Step 4: Ending the Session

To illustrate the behavior of temporary tables further, we should now quit all sessions. We can exit from the PostgreSQL prompt using the following command:

Query:

\q

Explanation:

When we end the session that created the mytemp table, PostgreSQL automatically drops the table. Therefore, it will no longer be available for querying in any future sessions.

Step 5: Reconnect and Query the Temporary Table

Finally, login to the database server again and query data from the ‘mytemp’ table:

SELECT * FROM mytemp; 

Output

Explanation:

The ‘mytemp' table does not exist because it has been dropped automatically when the session ended, therefore, PostgreSQL issued an error.

Important Points About PostgreSQL Temporary Table

• A temporary table is only visible to the session that creates it. Other sessions cannot access or see the temporary table.
• PostgreSQL drops temporary tables automatically at the end of the session or transaction, ensuring no leftover temporary data occupies space in the database.
• Each session’s temporary tables are isolated, preventing conflicts and ensuring data security.
• Be mindful of the session scope when creating temporary tables to avoid errors and ensure proper usage.

Conclusion

In conclusion, temporary tables in PostgreSQL offer a unique advantage by existing only during the duration of a session, providing a convenient way to handle session-specific data without affecting other users. Unlike permanent tables, which persist until explicitly dropped, temporary tables are automatically removed at the end of the session, ensuring a clean slate for each database interaction.