Temporary Table in PostgreSQL

How to Create a Temporary Table

The syntax to create a temporary table is as provided below:

• Creating a temporary table transaction-specific, where all rows are deleted on COMMIT.
  SQL

  CREATE [ GLOBAL|LOCAL ] {TEMPORARY | TEMP } TABLE table_name
  ( column_name data_type,
                ...
                         ...
                                   ... ) ON COMMIT DELETE ROWS;
  

• Creating a temporary table transaction-specific, where table is dropped on COMMIT.
  SQL

  CREATE [ GLOBAL|LOCAL ] {TEMPORARY | TEMP } TABLE table_name
   ( column_name data_type, 
                 ... 
                          ... 
                                    ... ) ON COMMIT DROP;

• Creating a temporary table session-specific.
  SQL

  CREATE [ GLOBAL|LOCAL ] {TEMPORARY | TEMP } TABLE table_name
   ( column_name data_type, 
                 ... 
                          ... 
                                    ... ) ON COMMIT PRESERVE ROWS;

Note:

1. In order to create a temporary table, you can use alternately TEMPORARY and TEMP.
2. GLOBAL and LOCAL keywords may be used optionally. They are depreciated and don't make any differences in PostgreSQL.

Some Examples of Usage

• may be used to convey data among triggers for either session or a transaction
• to store temporarily data for arithmetic

Differences between Temporary Tables in PostgreSQL and Oracle

CREATE [ GLOBAL|LOCAL ] 
{TEMPORARY | TEMP} TABLE table_name (
column_name data_type,
…
	…	
		… )[ ON COMMIT 
		{DELETE ROWS|PRESERVE ROWS|DROP }];

CREATE GLOBAL TEMPORARY TABLE 
table_name (
column_name data_type,
…
      …
            … )[ON COMMIT 
{DELETE ROWS | PRESERVE ROWS}]

Related reading about Oracle Global Temporary Table: Highlights of the Oracle database: Global Temporary Table