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.
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.
CREATE [ GLOBAL|LOCAL ] {TEMPORARY | TEMP } TABLE table_name
( column_name data_type,
...
...
... ) ON COMMIT DROP;
- Creating a temporary table session-specific.
CREATE [ GLOBAL|LOCAL ] {TEMPORARY | TEMP } TABLE table_name
( column_name data_type,
...
...
... ) ON COMMIT PRESERVE ROWS;
Note:
- In order to create a temporary table, you can use alternately
TEMPORARY
and TEMP
. 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
| PostgreSQL | Oracle | Comment |
Syntax |
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}]
|
- In Oracle,
GLOBAL keyword must be specified. - Oracle doesn't support
ON COMMIT DROP .
|
| | | |
Visibility | Both table definition and data are visible to the current session | The data in temporary table is private to each session. The definition of temporary table is visible to all sessions. | |
| | | |
Definition of temporary table | The definition isn't stored permanently. Each session must create it. | The definition is stored permanently. | PostgreSQL doesn't have very important feature that Oracle has: The definiton of temporary table remains after end of session. It's not necessary to perform many DDL operations. These operations shouldn't be a part of the system and should be used only in case to rebuild it. |
| | | |
Default | ON COMMIT PRESERVE ROWS is default in PostgreSQL | ON COMMIT DELETE ROWS is default in Oracle | |
Related reading about Oracle Global Temporary Table: Highlights of the Oracle database: Global Temporary Table