CREATE TEMPORARY TABLE statement creates a temporary table that is automatically dropped at the end of a session, or the current transaction (ON COMMIT DROP option).
-- Create a temporary table CREATE TEMPORARY TABLE temp_location ( city VARCHAR(80), street VARCHAR(80) ) ON COMMIT DELETE ROWS;
|Syntax (full...)|| CREATE [GLOBAL | LOCAL] TEMPORARY | TEMP TABLE [IF NOT EXISTS] name
( column definitions and constraints )
[ON COMMIT PRESERVE ROWS | DELETE ROWS | DROP]
|Visibility||Both table definition and data are visible to the current session only|
|Definition|| Is not stored permanently, each session must create it.
GLOBAL is for compatibility with ANSI/ISO SQL only and ignored
|Transaction Commit||PRESERVE ROWS||Nothing happens||This is the default|
|DELETE ROWS||All rows are deleted on COMMIT|
|DROP||The table is dropped on COMMIT|
Version: PostgreSQL 9.1
Before you can use a temporary table in a session, you must create the table as their definitions are not stored permanently. GLOBAL keyword is currently ignored by PostgreSQL:
CREATE TEMP TABLE temp_cities ( name VARCHAR(80) ) ON COMMIT DELETE ROWS;
ON COMMIT DELETE ROWS specifies that the data are removed from the temporary table at the end of each transaction:
BEGIN TRANSACTION; INSERT INTO temp_cities VALUES ('Warsaw'); INSERT INTO temp_cities VALUES ('Prague'); INSERT INTO temp_cities VALUES ('Milan'); SELECT COUNT(*) FROM temp_cities; -- Result: 3 COMMIT; SELECT COUNT(*) FROM temp_cities; -- Result: 0
Temporary tables in other databases:
CREATE GLOBAL TEMPORARY TABLE statement:
|Definition is stored permanently, visible to all sessions and not removed at the end of session|
|Each session can access only its own data|
|ON COMMIT||DELETE ROWS||This is the default (differs from PostgreSQL default)|
|PRESERVE ROWS||Rows deleted at the end of session|
Converting temporary tables to other databases:
Oracle stores the definitions of temporary tables permanently similar to the definitions of regular tables.
During the conversion, you usually need to extract CREATE TEMPORARY TABLE statements from application code, stored procedures, triggers etc. and execute them once to create the temporary table definitions.
CREATE GLOBAL TEMPORARY TABLE temp_cities ( name VARCHAR(80) ) ON COMMIT DELETE ROWS;