会话1中执行:
highgo=# create temporary table tmp_t (id int);
CREATE TABLE
highgo=# \d
List of relations
Schema | Name | Type | Owner
----------------+---------+-------+--------
oracle_catalog | dual | view | highgo
pg_temp_2 | tmp_t | table | highgo
public | bindump | table | highgo
public | hexdump | table | highgo
public | t | table | highgo
public | test | table | highgo
public | testa | table | highgo
public | testb | table | highgo
public | testc | table | highgo
public | testd | table | highgo
(10 rows)
从以上可以看出,临时表是在schema下生成的一个特殊的表,合格schema的名称为“pg_temp_xx”,其中xx代表一个数字,如2、3等,但不同的session这个数字是不同的。
会话2中执行:
highgo=# \d
List of relations
Schema | Name | Type | Owner
----------------+---------+-------+--------
oracle_catalog | dual | view | highgo
public | bindump | table | highgo
public | hexdump | table | highgo
public | t | table | highgo
public | test | table | highgo
public | testa | table | highgo
public | testb | table | highgo
public | testc | table | highgo
public | testd | table | highgo
(9 rows)
此时无法看到创建的临时表。
可以使用如下方式查看:
highgo=# \d pg_temp_2.tmp_t
Table "pg_temp_2.tmp_t"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
highgo=# select * from pg_temp_2.tmp_t;
id
----
(0 rows)
highgo=# insert into pg_temp_2.tmp_t values (1);
错误: 无法访问其它会话的临时表
highgo=# select * from pg_temp_2.tmp_t;
id
----
(0 rows)
highgo=#
会话1中执行:
highgo=# insert into tmp_t values (1);
INSERT 0 1
会话2中执行:
highgo=# select * from pg_temp_2.tmp_t;
错误: 无法访问其它会话的临时表
会话1中退出psql然后再进入psql:
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.
highgo=# \d
List of relations
Schema | Name | Type | Owner
----------------+---------+-------+--------
oracle_catalog | dual | view | highgo
public | bindump | table | highgo
public | hexdump | table | highgo
public | t | table | highgo
public | test | table | highgo
public | testa | table | highgo
public | testb | table | highgo
public | testc | table | highgo
public | testd | table | highgo
(9 rows)
此时无法看到临时表。
highgo=# select * from pg_temp_2.tmp_t;
错误: 关系 "pg_temp_2.tmp_t" 不存在
LINE 1: select * from pg_temp_2.tmp_t;
^
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
如果想要创建出事物级别的临时表,可以加‘ON COMMIT DELETE ROWS’字句,如下:
highgo=# create temporary table tmp_t (id int) ON COMMIT DELETE ROWS;
CREATE TABLE
highgo=# begin;
BEGIN
highgo=# insert into tmp_t values (1);
INSERT 0 1
highgo=# select * from tmp_t;
id
----
1
(1 row)
highgo=# end;
COMMIT
highgo=# select * from tmp_t;
id
----
(0 rows)
从上面可以看出,事物一结束,这种临时表中的数据就消失了。
退出psql后,临时表自动删除:
highgo=# select * from tmp_t;
id
----
(0 rows)
highgo=# \q
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.
highgo=# select * from tmp_t;
错误: 关系 "tmp_t" 不存在
LINE 1: select * from tmp_t;
^
ON COMMIT字句有一下三种方式:
(1)ON COMMIT PRESERVE ROWS:若不带ON COMMIT子名,默认情况下,数据会一直存在于整个会话周期中。
(2)ON COMMIT DELETE ROWS:数据只存在事物周期中,事物一提交,临时表就消失了。
(3)ON COMMIT DROP:数据只存在事物周期中,事物一提交,临时表就消失了。这种情况下,创建临时表的语句与插入数据的语句需要放在一个事物中,
若把创建临时表的语句放在一个单独的事物中,事物一结束,这张临时表就小时了。
创建临时表的关键字TEMPORARY也可以缩写成TEMP,如下两条语句是等价的:
create temporary table tmp_t (id int);
create temp table tmp_t (id int);
PG中为了兼容其他数据库,还设有GLOBAL和LOCAL关键字,但没有任何用处;如下几条SQL是完全等价的:
create temporary table tmp_t (id int);
create GLOBAL temporary table tmp_t (id int);
create local temporary table tmp_t (id int);