The temporary table is a table whose existence expires with the database session. In PostgreSQL, the working procedure of the temporary table is similar to the normal table. Normal tables get deleted only when the user deletes them willingly; however, the temporary tables get deleted automatically when the current session expires. Users can insert, delete and update values in a temporary table using respective queries.

This guide is to demonstrate the temporary table along with various examples in the PostgreSQL database as below.

• Create Temporary Table in PostgreSQL
• Insert Values in Temporary Table
• Drop Temporary Table in PostgreSQL
• Remove Temporary Table via Closing Session

Create Temporary Table in PostgreSQL

For creating a temporary table in PostgreSQL, the “CREATE TEMP TABLE” statement is used:

CREATE TEMP TABLE emp_table(
id INT,
name VARCHAR);

In this example, a temporary table “emp_table” has been created with two columns: “id” and “name”.

To verify the table’s creation, the user can execute the “SELECT” statement:

SELECT * FROM emp_table;

The “emp_table” has been created.

Insert Values in Temporary Table

Let’s learn how to insert values/records into an existing table. For this purpose, users must execute the “INSERT INTO” statement as follows:

INSERT INTO emp_table(Id,name)
VALUES (23, 'Peter');

In the above statement, the values “23” and “Peter” are added to the “id” and “name” columns of “emp_table”.

To extract the data from the temporary table, the user can utilize the “SELECT” statement, as shown below.

SELECT * FROM emp_table;

Output verifies that the desired values have been inserted into the “id” and “name” columns of the “emp_table”.

Drop Temporary Table in PostgreSQL

Before dropping any specific table, let’s check the list of existing tables in the database using “\d” command:

\d;

Suppose we want to delete the emp_table from the database. For this purpose, the “DROP TABLE” statement can be used as follows:

DROP TABLE emp_table;

Let’s authenticate the removal of temporary tables via the “\d” statement in PostgreSQL:

\d;

The “emp_table” has been successfully removed from the selected database.

Remove Temporary Table via Closing Session

Another way to drop a temporary table is by closing the current session. For this, a temporary table is created with a “CREATE TEMP TABLE”:

CREATE TEMP TABLE user_data (
id INT,
name VARCHAR);

A temporary table named “user_data” has been created successfully.

By ending the current session, the temporary table will be removed. For this, the command is written below:

\q

After executing the above command, Press any key to quit the current session. For verification of the removal of the temporary table, the “\d” statement is utilized as below:

\d;

Users can confirm that the targeted temporary table(i.e., user_data) has been successfully deleted from the database.

Conclusion

In Postgres, the “CREATE TEMP TABLE” statement is utilized to create the temporary table. Users can execute various commands like “SELECT”, “INSERT”, and “DROP” to perform various functionalities on the selected temporary table. The temporary table will be removed from the database after terminating the current session. This guide has covered all aspects regarding temporary tables in PostgreSQL.