Postgresql temporary tables

As the name indicates, PostgreSQL temporary tables are the tables that are created for some time and are removed manually or automatically. Manually removal deals with deleting the table. Whereas the table is automatically removed when the database session is ended. The temporary tables last till the session exists.

The use of a temporary table helps in avoiding the overloading of data in a particular database. Whenever you think you need to enter data in a database for some time to perform operations on the data, you will go for the temporary table. So that after usage, they are removed. And your database will have sufficient free space. This tutorial will explore what a temporary table is, its working, and its usage.

• Via Postgresql terminal (psql)
• Vis pgAdmin

The method of creation of a temporary table is the same as it is for the permanent table. Only the “temporary” word is added in the command.

Syntax

You can also use “temp” at the place of “temporary”. First, state the table name. And then mention the column name you need to add. A table that is created temporarily is seen only in the session for that it is created. For other sessions, it is not visible, just as it is not created ever.

Install and configure PostgreSQL in your systems. For authentication, apply a password to it. To create temporary tables in the database, you first have to create a database that will be used temporarily. After usage, it can be then deleted from PostgreSQL.

Moving towards the implementation process, open the psql terminal, and connect to the server. “Postgres” is the name set as by default.

Now create a new sample database. The database is named “abc”.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-01.png" data-lazy- height="306" src="data:image/svg xml,” width=”899″>

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-02.png" data-lazy- height="60" src="data:image/svg xml,” width=”422″>

After creating the database, you can check all the database information by using a PostgreSQL “lost of databases” command.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-03.png" data-lazy- height="204" src="data:image/svg xml,” width=”974″>

“l” is always used for enlisting any feature or detail of either file, databases, tables, etc. The temporary created database “abc” is shown at the top of the list. This list shows the owner of the database, which is Postgres. Other information also includes “access privileges”, which means that who can use these databases. After successfully forming the database, we need to connect to the database through a query of database connectivity.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-04.png" data-lazy- height="70" src="data:image/svg xml,” width=”904″>

The user “Postgres” makes a connection with the database “abc”. You can see that the database name in the psql has been changed from Postgres to the “abc”. From now onwards, the “abc” database is used till the session is not ended.

We will create a dummy table in the ‘abc’ database.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-05.png" data-lazy- height="168" src="data:image/svg xml,” width=”610″>

After the creation of the table, we confirm the table creation using the “select” command. This was a trail table creation to see whether the new database was working correctly or not.

Two tables will be created in abc database. One is permanent, and the other is a temporary table. The difference in both will be explained later in this guide.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-06.png" data-lazy- height="62" src="data:image/svg xml,” width=”914″>

This table consists of two columns. The name column is set for the primary key. Now we will create a temporary table. The syntax uses the keyword “temporary” in the table creation.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-07.png" data-lazy- height="56" src="data:image/svg xml,” width=”779″>

For both the tables, the message is displayed that shows that the table is created. Both the names of permanent and temporary tables can be matched with one another, but it is not mandatory. Once both the tables are created, we will select the data by using the “select” statement.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-08.png" data-lazy- height="106" src="data:image/svg xml,” width=”402″>

When we use the select statement to see the column names to identify the table, it’s either permanent or temporary. You will observe that it is a temporary table, as we have created a single column in the temporary table. It is clear from the above phenomena that in the presence of a temporary table, it is impossible to fetch the permanent table’s record. Both the names of tables are the same, so the temporary table will always be fetched.

It is proved that if both names are the same, we cannot fetch the details of the permanent table until we have removed the temporary table from the database.

We don’t need to describe the schema for the temporary relation. The reason is that the temporary tables are formed in the specified schema by Postgresql itself.

If we want to display all the tables created in the database “abc”, we will see that only the temporary table is enlisted but not the permanent one.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-09.png" data-lazy- height="187" src="data:image/svg xml,” width=”543″>

“dt” is used to display the database tables. You can see that the two temporary tables we created are displayed with information. The schema for temporary tables is “pg_temp_10”.

Now we will delete/drop the temporary table by using the drop command. After deletion, its all relation with the database and schema will be lost, and all privileges will be assigned to the permanent table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-10.png" data-lazy- height="54" src="data:image/svg xml,” width=”327″>

Drop table used the same concept of using the select command. While using select statements, the temporary table was fetched. Similarly, when we drop the table, the temporary table will be deleted first. Using the drop statement twice will delete both the tables. It is not mentioned in any command whether the table used in the command is temporary or permanent. So you have to consider it yourself.

After deletion of the temporary table, when we enlist all the tables of the “abc” database again.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-11.png" data-lazy- height="175" src="data:image/svg xml,” width=”575″>

You will see that a temporary and a permanent table is mentioned. The “public” schema is an indication of permanent tables. We can check it by displaying all the permanently created tables.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-12.png" data-lazy- height="504" src="data:image/svg xml,” width=”539″>

The below image shows the names and schema of each table. The names of permanent tables can never be the same as temporary tables.

Now you can connect back to the original database you were using before.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-13.png" data-lazy- height="79" src="data:image/svg xml,” width=”812″>

You are switched from ‘abc’ to ‘Postgres database.

Via pgAdmin

To understand the creation of a temporary table, first, move towards the left menu bar. Navigate towards the servers. The system will ask for the password. After providing the password, your connection with the server will be created. As we have created a database “abc” in psql, you will see that it shows two databases when we expand the PostgreSQL 13.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-14.png" data-lazy- height="418" src="data:image/svg xml,” width=”474″>

Now further expand the database “abc” and go towards the table portion.

<img alt="" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-15.png" height="181" src="data:image/svg xml,” width=”268″>

As we have deleted the temporary table in psql, so it is not present here. The permanent table “ books” is shown.

Now we will use an insert statement to check the working

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-16.png" data-lazy- height="318" src="data:image/svg xml,” width=”968″>

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Postgresql-temporary-tables-17.png" data-lazy- height="337" src="data:image/svg xml,” width=”637″>

Conclusion

This article “PostgreSQL temporary table” is a mesh of both implementation and visualization of temporary tables in the terminal and pgAdmin as well. The creation, working, and deletion of the temporary tables are explained with suitable examples. Yet precise but accurate information will be sufficient that leads to access to users.