PostgreSQL两种临时表：一种基于会话级的临时表；一种是事务级的临时表。
PG的版本及当前user：
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
1. 基于会话级的临时表测试，会话结束时临时表和临时表中的数据都会消失，而ORACLE中只是表中的数据消失，表还存在：
第一个会话：
postgres=# \c db03
You are now connected to database "db03" as user "postgres".
db03=# select pg_backend_pid();
pg_backend_pid
----------------
2688
(1 row)
db03=# create temporary table temp1(id int,name text);
CREATE TABLE
db03=# create table t1(id int,name text);
CREATE TABLE
db03=# \d
List of relations
Schema | Name | Type | Owner
-----------+-------+-------+----------
pg_temp_5 | temp1 | table | postgres
public | t1 | table | postgres
(2 rows)
db03=# insert into temp1 values(1,'xxx1'),(2,'xxx2');
INSERT 0 2
db03=# select * from temp1;
id | name
----+------
1 | xxx1
2 | xxx2
(2 rows)
db03=# select * from pg_temp_5.temp1;
id | name
----+------
1 | xxx1
2 | xxx2
(2 rows)
会话2：
postgres=# \c db03
You are now connected to database "db03" as user "postgres".
db03=# select pg_backend_pid();
pg_backend_pid
----------------
2733
(1 row)
db03=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
db03=# \d pg_temp_5.temp1
Table "pg_temp_5.temp1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
db03=# select * from pg_temp_5.temp1;
ERROR: cannot access temporary tables of other sessions
会话1：
db03=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
2793
(1 row)
postgres=# \c db03
You are now connected to database "db03" as user "postgres".
db03=# select pg_backend_pid();
pg_backend_pid
----------------
2795
(1 row)
db03=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
2. 基于事务级的临时表测试，ON COMMIT DELTE ROWS数据只存在于事务周期中，事务提交后数据就消失了；但表还存在，需会话退出才会不存在：
db03=# select current_database();
current_database
------------------
db03
(1 row)
db03=# select pg_backend_pid();
pg_backend_pid
----------------
2795
(1 row)
db03=# create temporary table temp2(id int,name text) on commit delete rows;
CREATE TABLE
db03=# \d
List of relations
Schema | Name | Type | Owner
-----------+-------+-------+----------
pg_temp_5 | temp2 | table | postgres
public | t1 | table | postgres
(2 rows)
db03=# begin;
BEGIN
db03=# insert into temp2 values(1,'xx1'),(2,'xx2');
INSERT 0 2
db03=# select * from temp2;
id | name
----+------
1 | xx1
2 | xx2
(2 rows)
db03=# end;
COMMIT
db03=# select * from temp2;
id | name
----+------
(0 rows)
db03=# \d
List of relations
Schema | Name | Type | Owner
-----------+-------+-------+----------
pg_temp_5 | temp2 | table | postgres
public | t1 | table | postgres
(2 rows)
db03=# select current_database();
current_database
------------------
db03
(1 row)
db03=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
db03=# \d pg_temp_5.temp2
Table "pg_temp_5.temp2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
db03=# select pg_backend_pid();
pg_backend_pid
----------------
2733
(1 row)
db03=# \d pg_temp_5.temp2
Table "pg_temp_5.temp2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
ON COMMIT DROP：数据只存在事务周期中，事务提交后临时表就消失了。这种情况下，创建临时表的语句与插入数据的语句需要放到一个事务中，若把创建临时表的语句在单独的事务中，事务一旦结束，这张临时表就会消失（表中数据及表）
db03=# create temporary table temp3(id int,name text) on commit drop;
CREATE TABLE
db03=# \d temp3
Did not find any relation named "temp3".
db03=# begin;
BEGIN
db03=# create temporary table temp3(id int,name text) on commit drop;
CREATE TABLE
db03=# \d temp3
Table "pg_temp_5.temp3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
db03=# insert into temp3 values(3,'xx3'),(4,'xx4');
INSERT 0 2
db03=# select * from temp3;
id | name
----+------
3 | xx3
4 | xx4
(2 rows)
db03=# end;
COMMIT
db03=# \d temp3
Did not find any relation named "temp3".
db03=# \d
List of relations
Schema | Name | Type | Owner
-----------+-------+-------+----------
pg_temp_5 | temp2 | table | postgres
pg_temp_5 | temp3 | table | postgres
public | t1 | table | postgres
(3 rows)
db03=# \d temp3
Did not find any relation named "temp3".
db03=# \d pg_temp_5.temp3
Did not find any relation named "pg_temp_5.temp3".
3. 可以通过pg_class的relpersistence=‘t'查询临时表。
db03=# select relowner,relname,relpersistence,relkind from pg_class
db03-# where relpersistence='t';
relowner | relname | relpersistence | relkind
----------+----------------------+----------------+---------
10 | pg_toast_16656 | t | t
10 | pg_toast_16656_index | t | i
10 | temp1 | t | r
10 | pg_toast_16662 | t | t
10 | pg_toast_16662_index | t | i
10 | temp2 | t | r
(6 rows)
可以通过pg_tables的tablename查询schemaname和tableowner
db03=# select schemaname,tablename,tableowner from pg_tables
db03-# where tablename like 'temp%';
schemaname | tablename | tableowner
------------+-----------+------------
pg_temp_7 | temp1 | postgres
pg_temp_5 | temp2 | postgres
(2 rows)