PostgreSQL支持两类临时表,一种是会话级的临时表,一种是事务级的临时表。在会话级别的临时表中,数据可以一直保存在整个会话的生命周期,而在事务级别的临时表,数据只存在这个事务的生命周期中。
在PostgreSQL中,不管是事务级的临时表还是会话级的临时表,当会话结束时,临时表就会消失,这与Oracle数据库不同。在Oracle数据库中,只是临时表中的数据消失,而临时表还存在。
如果在两个不同的session中创建一个同名的临时表,实际上创建的还是不同的两张表。
创建临时表
testdb=# create TEMPORARY table tmp_t1(id int primary key, note text);
在本session中可以看到这张表:
testdb=# \d
List of relations
Schema | Name | Type | Owner
-----------+--------+-------+----------
pg_temp_4 | tmp_t1 | table | postgres
可以看出临时表在schema pg_temp_4,其中4代表一个数字,不同的session数字不同
另开一个sql
-bash-4.2$ psql testdb
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | e | table | postgres
public | t | table | postgres
public | t1 | table | postgres
在另一个session中,直接用\d命令看不到这个临时表,试着加上schema
testdb=# \d pg_temp_4.tmp_t1
Table "pg_temp_4.tmp_t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
note | text | | |
Indexes:
"tmp_t1_pkey" PRIMARY KEY, btree (id)
可以查到这个临时表,查询数据看看,并不能访问表中的数据
testdb=# select * from pg_temp_4.tmp_t1;
ERROR: cannot access temporary tables of other sessions
在第一个session中插入数据
testdb=# insert into tmp_t1 values(1,'1111');
INSERT 0 1
testdb=# insert into tmp_t1 values(2,'2222');
INSERT 0 1
testdb=# select * from tmp_t1;
id | note
----+------
1 | 1111
2 | 2222
在默认情况下,创建的临时表时会话级的,如果想创建出事务级的临时表,可以加"ON COMMIT DELETE ROWS"
testdb=# create TEMPORARY table tmp_t2(id int primary key, note text) on commit delete rows;
testdb=# begin;
testdb=*# insert into tmp_t2 values(1,'aaaa');
testdb=*# insert into tmp_t2 values(2,'bbbb');
testdb=*# select * from tmp_t2;
id | note
----+------
1 | aaaa
2 | bbbb
(2 rows)
testdb=*# commit;
COMMIT
testdb=# select * from tmp_t2;
id | note
----+------
(0 rows)
可以看出,事务一结束,这种临时表中的数据就消失了。
ON COMMIT子句有3种形式:
ON COMMIT PRESERVE ROWS: 不带ON COMMIT,默认情况下,会话级临时表
ON COMMIT DELETE ROWS: 数据只存在于事务周期,事务一提交,数据就消失了。
ON COMMIT DROP: 数据只存在于事务周期,事务一提交,临时表就消失。这种情况,创建临时表的语句与插入数据的语句要放到一个事务中。
创建临时表的关键字TEMPORARY可以缩写为TEMP,下面2条命令等价
create temporary table tmp_t1(id int primary key, note text)
create TEMP table tmp_t1(id int primary key, note text)