语法
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
GLOBAL和LOCAL在这个语法中是一样的, 没有分别, 但是在SQL标准中是不一样的.
ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP
PRESERVE ROWS 表示临时表的数据在事务结束后保留.
DELETE ROWS 表示临时表的数据在事务结束后truncate掉.
DROP 表示临时表在事务结束后删除.
默认使用的是PRESERVE ROWS.
示例
临时表在会话结束后会自动删除（或者在事务结束后删除 on commit drop）.
会话1 :
pg9.2.0@db-172-16-3-150-> psql digoal digoal psql (9.2.0) Type "help" for help. digoal=> create temp table t(id int); CREATE TABLE digoal=> select relname,relnamespace,oid from pg_class where relname='t'; relname | relnamespace | oid ---------+--------------+------- t | 41192 | 41203 (1 row) digoal=> select nspname from pg_namespace where oid=41192; nspname ----------- pg_temp_2 (1 row)
退出会话1后重进, 临时表已经被删除了。
digoal=> \q pg9.2.0@db-172-16-3-150-> psql digoal digoal psql (9.2.0) Type "help" for help. digoal=> select nspname from pg_namespace where oid=41192; nspname ----------- pg_temp_2 (1 row) digoal=> select relname,relnamespace,oid from pg_class where relname='t'; relname | relnamespace | oid ---------+--------------+----- (0 rows)
每个会话中需要使用临时表的话需要重新创建. 好处是不同的会话能够使用同名但是不同结构的临时表。
会话 1
pg9.2.0@db-172-16-3-150-> psql digoal digoal psql (9.2.0) Type "help" for help. digoal=> create temp table t(id int); CREATE TABL
会话 2
pg9.2.0@db-172-16-3-150-> psql digoal digoal psql (9.2.0) Type "help" for help. digoal=> create temp table t(id text,id2 int); CREATE TABLE digoal=> select relname,relnamespace,oid from pg_class where relname='t'; relname | relnamespace | oid ---------+--------------+------- t | 11194 | 41206 t | 41192 | 41209 (2 rows) digoal=> select nspname from pg_namespace where oid in (11194, 41192); nspname ----------- pg_temp_1 pg_temp_2 (2 rows)
会话3
pg9.2.0@db-172-16-3-150-> psql digoal digoal psql (9.2.0) Type "help" for help. digoal=> create temp table t(id text,id2 int,info text); CREATE TABLE digoal=> select relname,relnamespace,oid from pg_class where relname='t'; relname | relnamespace | oid ---------+--------------+------- t | 11194 | 41206 t | 41192 | 41209 t | 41215 | 41217 (3 rows) digoal=> select nspname from pg_namespace where oid in (11194, 41192, 41215); nspname ----------- pg_temp_1 pg_temp_2 pg_temp_3 (3 rows)
