Temporary Table in PostgreSQL






4.82/5 (4 votes)
Temporary table is a special table that is bound to a transaction or to a session. It means that the data in the temporary table and the definition lasts till the end of the transaction or session.
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
andTEMP
. GLOBAL
andLOCAL
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}]
|
|
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