Introduction to PostgreSQL Temporary Table
The PostgreSQL has a very useful database feature that has an ability to create temporary tables for a current transaction or for the database session. It is helpful in managing the unprocessed data. As the name specifies the temporary table is having a short life span and it is not getting stored permanently, every time we have to create the temporary table for each transaction or database session. The temporary table exists in the database for a duration of a particular database session or in the current transaction. The temporary tables are automatic gets dropped at the end of the current transaction or at the end of the database session. The temporary tables are invisible to other transactions and database sessions.
Syntax:
In order to create a temporary table we have to use the CREATE TEMPORARY TABLE statement as follows:
CREATE TEMPORARY TABLE temp_table_name( … );
Also, we can use TEMP keyword instead of the TEMPORARY keyword in the CREATE statement defined above, and can rewrite it as follows:
CREATE TEMP TABLE temp_table_name( … );
How Temporary Table works in PostgreSQL?
- The temporary tables are invisible to other transactions and database sessions.
- The temporary tables are visible to the current transaction or database session in which we create the table.
- We can create a temporary table with the same name as a permanent table in the database which is actually not recommended. The temporary table hides the permanent table is defined with the same name until it is not dropped for the respective database session or transaction.
- We can not access the permanent table if we are having a temporary table with the same name as a permanent table.
How to Create PostgreSQL Temporary Table?
Here we can use either TEMP or TEMPORARY keyword with CREATE table statement for creating a temporary table.
Consider the following example which creates two tables ‘student’ and ‘teacher’ with the help of TEMP and TEMPORARY keyword with CREATE TABLE statements respectively.
1. With TABLE Keyword
CREATE TEMP TABLE student(stud_id serial NOT NULL PRIMARY KEY, stud_name VARCHAR(80));
Illustrate the created table using the above statement with the help of the following statement and syntax:
select * from student;
2. With TEMPORARY Keyword
CREATE TEMPORARY TABLE teacher(teacher_id serial NOT NULL PRIMARY KEY, teacher_name VARCHAR(80));
Illustrate the created table using the above statement with the help of the following statement and syntax:
Select * from teacher;
Now, consider that we have one table named ‘users’ already exists in the database which permanent table.
We will create the permanent ‘users’ table by using the following CREATE table statement.
CREATE TABLE users(id int, name VARCHAR(80));
Now, we will add some data into the permanent ‘users’ table. The following INSERT INTO statement will add some records in permanent ‘users’ table.
INSERT INTO users(id, name) VALUES (1,'Oliver'), (2,'Jake'), (3,'Harry');
Illustrate the result of the above statement by using the following snapshot and SQL statement.
Select * from users;
Output:
Now we will create a temporary table having the same name ‘users’ as the permanent table we have created.
We will create the temporary ‘users’ table by using the following CREATE table statement.
CREATE TEMPORARY TABLE users(id int, name VARCHAR(80));
Now, we will add some data into the temporary ‘users’ table. The following INSERT INTO statement will add some records in the temporary ‘users’ table.
INSERT INTO users(id, name) VALUES (4, 'Callum');
Now we are having users table which is permanent as well as temporary, but in PostgreSQL, the temporary table hides the definition of the permanent table within the transaction or database session.
So here we will try to get all records from users table using the following statement.
select * from users;
Output:
Illustrate the result of the above statement by using the following snapshot.
In the above snapshot, we can see the result set returned is from temporary users table not from permanent users table.
How to Insert Data in PostgreSQL Temporary Table?
Now, we will add some data into a temporary table created in the CREATE TABLE section.
The following INSERT INTO statement will add some records in both ‘student’ and ‘teacher’ tables.
INSERT INTO student(stud_name) VALUES ('Oliver'), ('Jake'), ('Harry'), ('Callum');
Illustrate the result of the above statement by using the following snapshot and SQL statement.
select * from student;
Output:
INSERT INTO teacher(teacher_name) VALUES ('Jack'), ('Connor'), ('Jacob'), ('John');
Illustrate the result of the above statement by using the following snapshot and SQL statement.
select * from teacher;
Output:
How to Delete PostgreSQL Temporary Table?
In order to drop a temporary table, we use the DROP TABLE statement as follows.
DROP TABLE temp_table_name;
Consider the following example which will delete both the ‘student’ and ‘teacher’ tables created in the CREATE table section above:
The following statement will delete the student table.
DROP TABLE student;
The following statement will delete the teacher table.
DROP TABLE teacher;
It is not needed to mention the TEMP or TEMPORARY keyword for dropping a table like we have to define these keywords for the CREATE TABLE statement.
Conclusion
Here we have seen how to use the PostgreSQL Temporary tables and how the PostgreSQL Temporary tables work to store the data. Also, we have added some examples of PostgreSQL Temporary tables.
Recommended Articles
This is a guide to PostgreSQL Temporary Table. Here we discuss how temporary table works, create, insert and delete in PostgreSQL with examples. You may also have a look at the following articles to learn more –
