大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全家桶1年46,售后保障稳定
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 ]
表示临时表的数据在事务结束后保留.
临时表的数据在事务结束后truncate掉.
表示
临时表在事务结束后删除.
PRESERVE ROWS.
临时表在会话结束后会自动删除(或者在事务结束后删除on commit drop).
pg9 . 2.0@db - 172 - 16 - 3 - 150 -> psql digoal digoalpsql ( 9.2 . 0 )Type "help" for help .digoal=> create temp table t(id int);CREATE TABLEdigoal=> 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)
digoal=> \qpg9.2.0@db-172-16-3-150-> psql digoal digoalpsql (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)
每个会话中需要使用临时表的话需要重新创建.
好处是不同的会话能够使用同名但是不同结构的临时表.
pg9.2.0@db-172-16-3-150-> psql digoal digoalpsql (9.2.0)Type "help" for help.digoal => create temp table t ( id int );CREATE TABLE
pg9.2.0@db-172-16-3-150-> psql digoal digoalpsql (9.2.0)Type "help" for help.digoal=> create temp table t(id text,id2 int);CREATE TABLEdigoal=> select relname,relnamespace,oid from pg_class where relname='t';relname | relnamespace | oid---------+--------------+-------t | 11194 | 41206t | 41192 | 41209(2 rows)digoal=> select nspname from pg_namespace where oid in (11194, 41192);nspname-----------pg_temp_1pg_temp_2(2 rows)
pg9.2.0@db-172-16-3-150-> psql digoal digoalpsql (9.2.0)Type "help" for help.digoal=> create temp table t(id text,id2 int,info text);CREATE TABLEdigoal=> select relname,relnamespace,oid from pg_class where relname='t';relname | relnamespace | oid---------+--------------+-------t | 11194 | 41206t | 41192 | 41209t | 41215 | 41217(3 rows)digoal=> select nspname from pg_namespace where oid in (11194, 41192, 41215);nspname-----------pg_temp_1pg_temp_2pg_temp_3(3 rows)
临时表可以选择在事务结束后删除数据或者保留数据或者删除表.
digoal=> begin;BEGINdigoal=> create temp table test (id int) on commit preserve rows;CREATE TABLEdigoal=> create temp table test1 (id int) on commit delete rows;CREATE TABLEdigoal=> create temp table test2 (id int) on commit drop;CREATE TABLEdigoal=> select relname,relnamespace,oid from pg_class where relname in ('test', 'test1', 'test2');relname | relnamespace | oid---------+--------------+-------test | 41215 | 41223test1 | 41215 | 41226test2 | 41215 | 41232(3 rows)digoal=> insert into test values (1);INSERT 0 1digoal=> insert into test1 values (1);INSERT 0 1digoal=> commit;COMMIT
digoal=> select relname,relnamespace,oid from pg_class where relname in ('test', 'test1', 'test2');relname | relnamespace | oid---------+--------------+-------test | 41215 | 41223test1 | 41215 | 41226(2 rows)
digoal=> select * from test;id----1(1 row)
digoal=> select * from test1;id----(0 rows)
digoal=> select * from test2;ERROR: relation "test2" does not existLINE 1: select * from test2;^
digoal=> create table dup_table_name (id int);CREATE TABLEdigoal=> create temp table dup_table_name (id int);CREATE TABLEdigoal=> insert into digoal.dup_table_name values (1);INSERT 0 1digoal=> select * from dup_table_name ;id----(0 rows)digoal=> insert into dup_table_name values (2);INSERT 0 1digoal=> select * from dup_table_name ;id----2(1 row)digoal=> select * from digoal.dup_table_name ;id----1(1 row)
digoal=> create index idx_test on dup_table_name (id);CREATE INDEXdigoal=> \d dup_table_nameTable "pg_temp_3.dup_table_name"Column | Type | Modifiers--------+---------+-----------id | integer |Indexes:"idx_test" btree (id)digoal=> \di idx_testList of relationsSchema | Name | Type | Owner | Table-----------+----------+-------+--------+----------------pg_temp_3 | idx_test | index | digoal | dup_table_name(1 row)
digoal=> create temp table digoal.tmp_test (id int);ERROR: cannot create temporary relation in non-temporary schema
【参考】
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/210102.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...