Temporary Tables

Temporary tables are short-lived tables--they exist only for the duration of a database session. When a database session terminates, its temporary tables are automatically destroyed. Figure  illustrates this concept.  

        $ psql test
        Welcome to psql, the PostgreSQL interactive terminal.
         
        Type:  \copyright for distribution terms
               \h for help with SQL commands
               \? for help on internal slash commands
               \g or terminate with semicolon to execute query
               \q to quit
         
        test=> CREATE TEMPORARY TABLE temptest(col INTEGER);
        CREATE
        test=> SELECT * FROM temptest;
         col 
        -----
        (0 rows) 
         
        test=> \q
        $ psql test
        Welcome to psql, the PostgreSQL interactive terminal.
         
        Type:  \copyright for distribution terms
               \h for help with SQL commands
               \? for help on internal slash commands
               \g or terminate with semicolon to execute query
               \q to quit
         
        test=> SELECT * FROM temptest;
        ERROR:  Relation 'temptest' does not exist  

In the figure, CREATE TEMPORARY TABLE creates a temporary table. On psql exit, the temporary table is destroyed. Restarting psql reveals that the temporary table no longer exists.

Temporary tables are visible only to the session that creates them; they remain invisible to other users. In fact, several users can create temporary tables with the same name, and each user will see only his version of the table. (See Table  for an example.)

 

Table: Temporary table isolation

User 1	User 2
CREATE TEMPORARY TABLE temptest (col INTEGER)	CREATE TEMPORARY TABLE temptest (col INTEGER)
INSERT INTO temptest VALUES (1)	INSERT INTO temptest VALUES (2)
SELECT col FROM temptest returns 1	SELECT col FROM temptest returns 2

Temporary tables even mask ordinary tables with the same name.

Temporary tables are ideal for holding intermediate data used by the current SQL session. For example, suppose you need to do many SELECTs on the result of a complex query. An efficient strategy is to execute the complex query once, then store the result in a temporary table.

As an example, Figure  uses SELECT ... INTO  TEMPORARY TABLE to collect all Pennsylvania customers into a temporary table.  

        test=> SELECT *
        test-> INTO TEMPORARY customer_pennsylvania
        test-> FROM customer
        test-> WHERE state = 'PA';
        SELECT
        test=> CREATE index customer_penna_custid_idx ON customer_pennsylvania (customer_id);
        CREATE  

It also creates a temporary index  on the temporary table. The customer_pennsylvania table can then be used in subsequent SELECT queries. Multiple users can perform this operation at the same time with the same temporary names without fear of collision.