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:
- The syntax to create a temporary table and how it is different from the SQL Server temp tables
- How to insert data in temporary tables
- How to use the temporary table in the Stored procedure
- View the temp table
- Drop temp table
Syntax to create PostgreSQL Temporary tables
The query syntax to create a temporary table is as the following.
|
1 2 3 4 5 6 7 8 |
create temporary table [TemptblName] ( column_1 datatype , column_2 datatype , ... ) |
In the syntax,
- Specify the TEMP or TEMPORARY keyword after the CREATE keyword
- 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:
|
1 2 3 |
postgres=# create temporary table Temp_tblSession(ID int); |
Screenshot
Now, let us try to access the tblSession1 from the second instance of the pSQL tool. You will receive the following error:
|
1 2 3 4 5 |
postgres=# select * from Temp_tblSession; ERROR: relation "temp_tblsession" does not exist LINE 1: select * from Temp_tblSession; |
Screenshot
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.
|
1 2 3 4 5 6 7 8 9 |
create temporary table tblStudent ( Student_id int, Student_name varchar(500), student_grade char(1), School_ID int ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TEMPORARY TABLE "tblMovies" ( "MovieTitle" character varying(500), description character varying(5000) NOT NULL, "Release_year" integer, duration integer, movie_length integer, rating varchar(5), PRIMARY KEY ("MovieTitle") ); |
Screenshot
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
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT title, description, release_year, rental_rate, rental_duration, length INTO TEMP TABLE tblnc17movies FROM film WHERE rating = 'NC-17'; |
Output
Once the query executes successfully, run the following SELECT statement to view data from the tblNC17Movies.
|
1 2 3 |
select * from tblNC17Movies limit 20 |
Output
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
- Create a temp table named tblFilm
- Inserts data of the movies in a named tblFilm whose rating is R
- Drop temp table named tblFilm
Following is the code of the stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE OR REPLACE PROCEDURE sp_get_movies() LANGUAGE plpgsql AS $procedure$ BEGIN CREATE TEMPORARY TABLE "tblfilm" ( MovieTitle character varying(500), duration integer, movie_length integer, rating varchar(5) ); insert into tblfilm (MovieTitle,duration,movie_length,rating) select title, rental_duration, length,rating from film where rating='R'; select * from tblFilm; drop table tblFilm; END; $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.
|
1 2 3 |
CALL sp_get_movies(); |
Output
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:
|
1 2 3 4 5 6 7 8 9 |
SELECT table_catalog, table_schema, table_name, table_type, FROM information_schema.tables WHERE table_catalog = 'DVDShop' AND table_type = 'LOCAL TEMPORARY'; |
Query Output
Drop temp table in PostgreSQL
The syntax to drop the temp table is following
|
1 2 3 |
DROP TABLE [TableName] |
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.
|
1 2 3 |
Drop table tblStudent; |
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
|
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:
- The syntax to create a temporary table and how it is different from the SQL Server temp tables
- How to insert data in temporary tables
- How to use the temporary table in the Stored procedure
- View the temp table
- 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.
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
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022