PostgreSQL - CREATE TEMPORARY TABLE - Guide, Examples and Alternatives

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

Quick Example:

   -- Create a temporary table
   CREATE TEMPORARY TABLE temp_location
   (
      city VARCHAR(80),
      street VARCHAR(80)
   ) 
   ON COMMIT DELETE ROWS;

Overview:

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:

Oracle:

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
	DROP		Not supported

Converting temporary tables to other databases:

Oracle:

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.

• GLOBAL keyword must be specified
• Oracle does not support ON COMMIT DROP, so if this option is required, you need to explicitly execute DROP TABLE statement after each COMMIT
• ON COMMIT PRESERVE ROWS is the default in PostgreSQL, while ON COMMIT DELETE ROWS is the default in Oracle

  CREATE GLOBAL TEMPORARY TABLE temp_cities
  (
     name VARCHAR(80)
  )
  ON COMMIT DELETE ROWS;