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