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



    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 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;
    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 INDEX dataimport_branch_id_idx on dataimport(branch_id);
    # CREATE INDEX dataimport_name_idx on dataimport(name);


    //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.description, di.price, existing_branch_id, existing_name
    # from dataimport di left join branch br on left join iteminfo ii on;


    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;

    After the uses of the temporary table, we can remove it manually also otherwise Postgres session will remove it automatically.


    --Thanks-- __________________________________



Tags: postgresql