PostgreSQL Temporary Table

In this section, we are going to understand the working of PostgreSQL temporary table and how to create and remove it.

How to Create a PostgreSQL temporary table

A temporary table is a brief table; its name means; it presents at the time of a database session. The PostgreSQL automatically drops the temporary tables at the end of an operation or a session.

Here, we are going to use the CREATE TEMPORARY TABLE command for creating a temporary table.

Syntax

The below syntax is used to create a temporary table in PostgreSQL:

Or

Instead of using the TEMPORARY keyword in the above syntax, we can also use the TEMP keyword.

Example of PostgreSQL temporary table

Let us see some examples to understand the working of the temporary table in real-time.

Here, we are creating a temp table in SQL shell (psql). So, for this, we will follow the below process:

Firstly, log in to the PostgreSQL database server using the psql and create a new database as Jtp with the help of below command:

Output

After executing the above command, we will get the below output, which says that the jtp database has been created successfully.

To see a list of all databases created earlier, we will enter the below command:

Output

After implementing the above command, we get the below result:

To connect to a database, we will enter the below command:

Output

After executing the above command, we get the below outcome:

Once we created a new database, we will create a temporary table named jti1 with the help of below command:

Output

Once we implemented the above command, we will get the below result:

After that, we will select the jti1 table using the SELECT command as follows:

Output

After executing the above command, we will get the below result:

Then, we will close the above session and launch another session connected to the jtp database and query data from the jti1 table as we can see in the below command:

Output

After executing the above command, we will get the below result:

As we see that, in this session, we could not see the jti1 table because only the first session can access it.

Then we will quit all the sessions with the help of below command:

Output

Once we executed the above command, we have to press any key to exit the psql window:

After that, we will log in to the database server again and get the data from the jti1 table:

Output

After executing the above command, we will get the below output, where the PostgreSQL issued an error as the jti1 table does not exist. And when the session ended, it removed automatically.

PostgreSQL temporary table name

A temporary table can share a similar name with a permanent table, while it is not compulsory. Or if we are creating a temporary table with a similar name as a permanent table, we cannot retrieve the permanent table until the temporary table is removed.

For example

Here, firstly we will be creating a table named with the help of below command:

Output

Once we implement the above command, we will get the below message window; the fruits table has been created successfully.

After that, we will create a temporary table which has a similar name to fruits.

Output

As we can see in the below image, the temporary table fruits has been created successfully.

Once both the tables have been created successfully, we will select the records from the fruits table by using the SELECT command:

Output

After executing the above command, we will get the below result where we can see that the PostgreSQL retrieved the temporary table fruits instead of permanent one.

When the temporary table fruits is explicitly deleted, we can only have the permanent fruits table in the existing session.

Note: We may not define the schema in the CREATE TEMP TABLE command because the PostgreSQL creates temporary tables in a particular schema.

If we list the tables in the Jtp database, we will only see the temporary table fruits, not the permanent one by using below command:

Output

As we can see in the below outcome that the schema of the fruits temporary table is pg_temp_3.

How to Drop a PostgreSQL temporary table

Here, we are dropping the temporary table with the help of the Drop table command.

The Syntax for dropping a PostgreSQL temporary table

The below syntax is used to remove a temporary table in PostgreSQL:

As compared to the CREATE TABLE command, the DROP TABLE command does not have the TEMP or TEMPORARY keyword concept precisely for temporary tables.

Let us see an example to understand the working of removing the temporary table.

Here, we will drop the temporary table fruits, which we have created in the above examples by using below command:

Output

After executing the above command, we will get the below message: the fruits table has been dropped successfully.

If we list the tables in the jtp database again by using the below command:

Output

Once we implement the above command, it will show the permanent table fruits in that particular list:

Overview

• We have learned about the temporary table.
• We create a temporary temp with the help of the CREATE TEMP TABLE command.
• To remove the temporary table, we use the DROP TABLE command.