sql标准中临时表是一次创建,以后使用的时候无须再次创建的. 并且每个会话保持各自的数据. 但是在Postgresql中,临时表的使用有所改变. 1. 临时表在会话结束后会自动删除（或者在事务结束后删除on commit drop）. 也就是说每个会话中需要使用临时表的话需要重新创建. 这个有好处也有坏处,好处是不同的会话能够使用同名但是不同结构的临时表. sql标准无法做到. 坏处是新建的会话如果只是要使用同名同结构的临时表也有重新创建. 2. 临时表可以选择在事务结束后删除数据或者保留数据或者删除表.
【语法】
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.
【例子】 1. 临时表在会话结束后会自动删除（或者在事务结束后删除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,oID from pg_class where relname='t'; relname | relnamespace | oID ---------+--------------+----- (0 rows)
2. 每个会话中需要使用临时表的话需要重新创建. 好处是不同的会话能够使用同名但是不同结构的临时表. 会话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会话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,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,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)
3. 临时表可以选择在事务结束后删除数据或者保留数据或者删除表.
digoal=> begin; BEGIN digoal=> create temp table test (ID int) on commit preserve rows; CREATE table digoal=> create temp table test1 (ID int) on commit delete rows; CREATE table digoal=> create temp table test2 (ID int) on commit drop; CREATE table digoal=> select relname,oID from pg_class where relname in ('test', 'test1', 'test2'); relname | relnamespace | oID ---------+--------------+------- test | 41215 | 41223 test1 | 41215 | 41226 test2 | 41215 | 41232 (3 rows) digoal=> insert into test values (1); INSERT 0 1 digoal=> insert into test1 values (1); INSERT 0 1 digoal=> commit; COMMIT事务提交后test2已经被自动drop掉了.
digoal=> select relname, 'test2'); relname | relnamespace | oID ---------+--------------+------- test | 41215 | 41223 test1 | 41215 | 41226 (2 rows)test的数据事务提交后数据保留.
digoal=> select * from test; ID ---- 1 (1 row)test1的数据事务提交后数据已删除.
digoal=> select * from test1; ID ---- (0 rows)test2在事务提交后表已删除.
digoal=> select * from test2; ERROR: relation "test2" does not exist liNE 1: select * from test2; ^
4. 如果有临时表和非临时表重名了,那么默认是使用临时表的,如果要使用非临时表,需要带上schema,如schema.table.
digoal=> create table dup_table_name (ID int); CREATE table digoal=> create temp table dup_table_name (ID int); CREATE table digoal=> insert into digoal.dup_table_name values (1); INSERT 0 1 digoal=> select * from dup_table_name ; ID ---- (0 rows) digoal=> insert into dup_table_name values (2); INSERT 0 1 digoal=> select * from dup_table_name ; ID ---- 2 (1 row) digoal=> select * from digoal.dup_table_name ; ID ---- 1 (1 row)
5. 临时表上创建的索引也是临时的.
digoal=> create index IDx_test on dup_table_name (ID); CREATE INDEX digoal=> \d dup_table_name table "pg_temp_3.dup_table_name" Column | Type | ModifIErs --------+---------+----------- ID | integer | Indexes: "IDx_test" btree (ID) digoal=> \di IDx_test List of relations Schema | name | Type | Owner | table -----------+----------+-------+--------+---------------- pg_temp_3 | IDx_test | index | digoal | dup_table_name (1 row)
6. 临时表无法选择性的创建在某个schema下面,它是存在于临时schema的,例如pg_temp_?. 对应的TOAST表也在临时的schema下,例如(pg_toast_temp_?) . 虽然无法选择schema但是tablespace是可以指定的.
digoal=> create temp table digoal.tmp_test (ID int); ERROR: cannot create temporary relation in non-temporary schema
7. Postgresql 中临时表的统计信息不会被autovacuum daemo自动收集. 所以如果有复杂查询的话,最好再有DML后自己执行analyze.
【小结】 1. 如果有临时表和非临时表重名了,如schema.table. 2. 临时表上创建的索引也是临时的. 3. 临时表无法选择性的创建在某个schema下面,例如(pg_toast_temp_?) . 虽然无法选择schema但是tablespace是可以指定的. 4. Postgresql 中临时表的统计信息不会被autovacuum daemo自动收集. 所以如果有索引的情况下,最好再有DML后自己执行analyze. 【参考】 总结
以上是内存溢出为你收集整理的PostgreSQL 临时表全部内容，希望文章能够帮你解决PostgreSQL 临时表所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）