Creation and Uses of a Temporary Table in PostgreSQL

Introduction

    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.

Prerequisites

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(
  -- 
);

Example

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 "iteminfo" table which also have some records. Here we just join the temporary table (dataimport) with branch and iteminfo table for getting the required records according to our need. The temporary table is select the records with actual tables and nothing

#
# 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.

--Thanks-- __________________________________