Creation and Uses of a Temporary Table in PostgreSQL
Posted By : Rahul Singh | 30-Nov-2018
Hi Guys, In this blog, I explain how we create a temporary table and when we used this temporary table in our SQL. First, we understand what is the temporary table, this table is just temporary bases means it stores the data for validation and filtration purpose. After the data is in well formed and according to the permanent table then it will dump into the actual table and then we will remove the temporary table.
To implement this example we should have a basic knowledge of PostgreSQL database and PostgreSQL version is 9.5 and also have basic CURD operations in the database.
The syntax for the creation of a Temporary table:
CREATE TEMPORARY TABLE emp_temp( -- ); OR CREATE TEMP TABLE emp_temp( -- );
The temporary table is valid with some time period (session) after that PostgreSQL will automatically remove this table after the transaction or time period completion.
Let's take an example to the creation of a Temporary table: First, connect with PostgreSQL with credentials:
postgres=# CREATE DATABASE demo; CREATE DATABASE postgres-# \c demo; You are now connected to database "demo" as user "postgres".
//Here we create the temp table and also create the index for that table.
# CREATE TEMP TABLE dataimport # ( # branch_id bigint, # name varchar(70), # description varchar(255), # price real # ); CREATE TABLE # CREATE INDEX dataimport_branch_id_idx on dataimport(branch_id); CREATE INDEX # CREATE INDEX dataimport_name_idx on dataimport(name); CREATE INDEX
//Insert the records into the temp table.
# # INSERT INTO dataimport (branch_id, name, description, price) VALUES # (21, 'item11','SPICEY PEANUTS',41.33), # (22, 'item22','GARLIC CHEES',12.17), # (98, 'item33','GARLIC SOUP',44.17), # (99, 'item44','SPICEY OATS',10.40); INSERT 0 4
Suppose we have a "branch" table which is not a temp table it has some records and one "
# # select di.branch_id, di.name, di.description, di.price, br.id existing_branch_id, ii.name existing_name # from dataimport di left join branch br on di.branch_id=br.id left join iteminfo ii on di.name=ii.name;
Output for the following query:
branch_id | name | description | price | existing_branch_id | existing_name ------------+----------------+------------------+-------+----------------------+---------------- 21 | item11 | SPICEY PEANUTS | 41.33 | 21 | 22 | item22 | GARLIC CHEES | 12.17 | 22 | item22 98 | item33 | GARLIC SOUP | 44.17 | | item33 99 | item44 | SPICEY OATS | 10.40 | | (4 rows)
# # drop table dataimport; DROP TABLE
After the uses of the temporary table, we can remove it manually also otherwise Postgres session will remove it automatically.
Rahul singh is a Java Developer and having experience in developing Applications. He is a quick learner.