"PostgreSQL" temp table

There are two types of temporal tables in PostgreSQL, one is the session-level temporary table and one is the transaction-level temporary table. In a session-level staging table, data can exist throughout the lifetime of the session, and the data in the transaction-level staging table can only exist in the life cycle of the transaction.
1. Session-Level staging table

# #创建会话级临时表, a session-level is created by default in PG. Test=#Create Temporary TableTMP1 (IDint Primary Key, notetext);CREATE TABLE# #查看表属性, the schema is "pg_temp_x", where "X" represents a number, different sessionzhegetest=# \d List ofRelationsSchema |Name|Type|Owner-----------+------+-------+----------Pg_temp_3|Tmp1| Table |Postgres (1row) # #退出会话test=# \q# #重新登录会话[[email protected] bin]$ psql Test;psql (8.4. -, the server9.6.3) Warning:psql version8.4, server version9.6. SomePsql features might not  Work. Type "Help" forhelp.# #再次查看, the temp table has disappeared test=# \dno relations found.

2. Transaction-level staging table
The "on commit delete Rows" clause is required to create a transaction-level temporary table statement.

# #创建事务级临时表test=#Create Temporary TableTMP2 (IDint Primary Key, notetext) on Commit Deleterows;CREATE TABLE# #开始一个事务test=#begin;BEGIN# #插入测试数据test=#Insert  intoTmp2Values(1,'Tom');INSERT 0 1Test=#Insert  intoTmp2Values(2,'Peter');INSERT 0 1# #查看表中数据test=#Select *  fromtmp2; ID|Note----+-------  1 |Tom2 |Peter (2rows) # #结束事务test=#End;COMMIT# #再次查看, the data in the table has disappeared because the data in the transaction-level staging table exists only in the lifetime of the transaction test=#Select *  fromtmp2; ID|Note----+------(0Rows

Summarize:
1. Whether it is a session-level or transaction-level temporal table, the temporary table disappears when the session ends, which differs from the Oracle database. Oracle database when the session ends, the data disappears, and the table persists.
2. The "on COMMIT" clause has three forms, using preserve ROWS by default:
(1) On COMMIT PRESERVE ROWS indicates that the data for the staging table is retained after the transaction ends;
(2) On COMMIT DELETE ROWS indicates that the data for the temporary table truncate out after the transaction ends;
(3) The On COMMIT drop indicates that the staging table was deleted after the transaction ended.

The end!

2017-08-15

"PostgreSQL" temp table