Nisarg Upadhyay
Drop temp table: run stored procedure

Drop temp tables in PostgreSQL

March 22, 2022 by

In this article, we are going to learn how we can drop the temp table in PostgreSQL. The temp table is used to store data temporarily. Suppose you want to format or manipulate the data using aggregate and string functions. So instead of processing and formatting the data in the base table, it is preferred to populate data from the base table and store it in a temporary table. You can process and format the data stored in a temporary table. The syntax to create a temporary table and regular table are the same. You can add indexes, constraints, statistics to the temporary tables. In this article, we are going to learn the following topics:

  1. The syntax to create a temporary table and how it is different from the SQL Server temp tables
  2. How to insert data in temporary tables
  3. How to use the temporary table in the Stored procedure
  4. View the temp table
  5. Drop temp table

Syntax to create PostgreSQL Temporary tables

The query syntax to create a temporary table is as the following.

In the syntax,

  1. Specify the TEMP or TEMPORARY keyword after the CREATE keyword
  2. Specify the list of columns with datatype after the name of the temp table

The temporary tables are session-specific tables and accessible within the scope of the execution of the user connection. To understand the concept, I have opened two instances of the pSQL command line tool. The first instance of the temporary table named tblSession1 has been created.

Query:

Screenshot

Create temp table

Now, let us try to access the tblSession1 from the second instance of the pSQL tool. You will receive the following error:

Screenshot

Multiple session can not access the temp table

The above scenario explains that the temporary tables can be accessed only by the session which has created them. Let us see some examples to understand the usage of the temporary table.

Create Temporary tables

We want to create a temporary table named tblStudent. The query to create a table is following.

Create a temporary table with indexes and constraints

We want to create a table named tblSchool. The table must have the primary key and a non-clustered index. To maintain the data integrity, we have added a NOT NULL constraint.

Screenshot

Create temp table with primary key

As you can see, the table has been created.

Use a temporary table in a SQL Query

In this example, we will see how to insert data in the PostgreSQL temporary table within the script. I want to copy the list of movies with an NC-17 rating to the temporary table named tblNC17Movies. To do that, we are using the SELECT * INTO TEMP TABLE.

Query

Output

Drop temp table: Select temp table into

Once the query executes successfully, run the following SELECT statement to view data from the tblNC17Movies.

Output

Drop temp table: View data from temp table

Now, let us see, how we can use them in a stored procedure.

Use a procedure table in a function

In this example, we will see how we can use the temporary tables in the PostgreSQL procedure. The procedure performs the following tasks

  1. Create a temp table named tblFilm
  2. Inserts data of the movies in a named tblFilm whose rating is R
  3. Drop temp table named tblFilm

Following is the code of the stored procedure:

Once the procedure is created, run the following command to execute the stored procedure; the procedure populates the list of movies with an R rating.

Output

Drop temp table: run stored procedure

As you can see, the query has populated a list of movies that has an R rating.

View PostgreSQL temporary tables

The temporary tables are created in the pg_temp schema of PostgreSQL. To view the temporary tables, run the following query:

Query Output

Drop temp table: View temp tables

Drop temp table in PostgreSQL

The syntax to drop the temp table is following

As you can see in the syntax, you must specify the table name after the DROP TABLE statement. As I mentioned, the temp tables are session-specific. They can be dropped by the session which has created it. To drop the tblStudent table, run the following query.

Difference between SQL Server temp table and PostgreSQL temp table

Feature

PostgreSQL Temporary table

SQL Server Temporary table

Definition

Table name does not require any prefix.

The name must have # or ## as a prefix of the table.

Example

  • #tblexample local temp table
  • ##tblemaple global temp table.

Supports Global temporary table

No

Yes

Supports table variable

No

Yes

The same name of a temporary table and base table

Yes

No

Location

Stored in pg_temp schema

Stored in TempDB database

Summary

In this article, we learned about the temporary tables in PostgreSQL. We have understood the following points:

  1. The syntax to create a temporary table and how it is different from the SQL Server temp tables
  2. How to insert data in temporary tables
  3. How to use the temporary table in the Stored procedure
  4. View the temp table
  5. Drop temp table

In the next article, we will learn how we can create temporary tables in oracle and how they are different from the SQL Server and PostgreSQL.

Nisarg Upadhyay
Languages and coding, PostgreSQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

10,075 Views