PostgreSQL Temporary Tables

PostgreSQL has reliable database features for handling different transactions. For instance, its temporary table saves data per given session. A temporary table exists for a given session and closing the given session or trying to query the same temporary table in another session will return an error.

This post will guide you in understanding how temporary tables work. We will create a temporary table, then try to access it from another session.

Working with Temporary Tables in PostgreSQL

Before you work with a temporary table in PostgreSQL, let’s understand a few facts.

1. The temporary table is only visible in a given session. Other transactions or databases cannot access a given transaction.
2. You can have a temporary table with the same name as a permanent table in the same database.
3. When a temporary table shares the same name as a permanent table, the permanent table cannot be accessed until the temporary table gets dropped from the database or session.

Here is the syntax for creating a temporary table in PostgreSQL. You can use the TEMPORARY or TEMP keyword to create it.

 
or

 
In this tutorial, we will create a database, create a temporary table, and a permanent table. Then, try accessing the same temporary table from another session to understand how a temporary table works.

Access your PostgreSQL console and create a database. Let us name ours linuxhint.

 

You will get confirmation that your database was created successfully. Next, connect to the created database.

 

Create a permanent table. Let us name it names.

 

With our permanent table created, let us create a temporary table with the same name but different columns.

 

We can verify that our database contains the two created tables using the \dt command.

You will note that only one table is listed as the temporary table is not listed. However, if we list the contents of the particular table, it displays the contents of the temporary table. The permanent table is “silenced” until the temporary table gets dropped.

Suppose you open another PostgreSQL session and connect to the created database. If you try accessing the temporary table, it will not exist. Instead, the permanent table will get retrieved as the temporary table only exists in the session where it got created.

In the output, we get the contents of the permanent table contrary to what we got when we ran the same command in the session containing the temporary table.

Still, if you quit the current session, then come back later, the temporary table will not exists.

Try quitting by running the \q command, then try to access the temporary table. It will return an error.

Inserting Values Into a Temporary Table

You can insert values to a temporary table the same way you insert them on a permanent table.

Once inserted, you can confirm the temporary table contains the inserted values as expected.

Dropping a Temporary Table

You can use the drop command if you want to delete the temporary table without waiting for it to automatically get deleted when the session expires.

Conclusion

Temporary tables are tables created in PostgreSQL with a short lifespan. The tables are only visible in the current session. Once you exit the session, the table gets automatically deleted. Still, if you open another session and try accessing the temporary table, it will return an error. When you have a temporary and permanent table sharing the same name, you can’t access the permanent table until you drop the temporary table.