PostgreSQL 临时表[通俗易懂]

PostgreSQL 临时表[通俗易懂]转载自: http://blog.163.com/digoal@126/blog/static/1638770402012101575032326/SQL标准中临时表是一次创建,以后使用的时候无须再次创建的.并且每个会话保持各自的数据.但是在PostgreSQL中,临时表的使用有所改变.1.临时表在会话结束后会自动删除(或者在事务结束后删除oncommitdrop)

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全家桶1年46,售后保障稳定


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 ]

Jetbrains全家桶1年46,售后保障稳定

红色部分是与临时表有关的. 其中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,relnamespace,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,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)


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,relnamespace,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,relnamespace,oid from pg_class where relname in ('test', 'test1', '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, 如schema.table.
2. 临时表上创建的索引也是临时的.
3. 临时表无法选择性的创建在某个schema下面, 它是存在于临时schema的, 例如pg_temp_?. 对应的TOAST表也在临时的schema下, 例如(pg_toast_temp_?) . 虽然无法选择schema但是tablespace是可以指定的.
4. PostgreSQL 中临时表的统计信息不会被autovacuum daemo自动收集. 所以如果有索引的情况下, 最好再有DML后自己执行analyze.

【参考】

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/210102.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • stringutil.isnotempty_中低腰和低腰的区别

    stringutil.isnotempty_中低腰和低腰的区别
    转自:http://www.zhenhua.org/article.asp?id=625
     
    isNotEmpty将空格也作为参数,isNotBlank则排除空格参数

    参考QuoteStringUtils方法的操作对象是java.lang.String类型的对象,是JDK提供的String类型操作方法的补充,并且是null安全的(即如果输入参数String为null则不会抛出NullPointerException,而是做了相应处理,例如,如果输入为

  • System.setProperty()「建议收藏」

    System.setProperty()「建议收藏」/**设置指定键对值的系统属性*setProperty(Stringprop,Stringvalue);**參数:*prop-系统属性的名称。*value-系统属性的值。

  • redis通过6379端口无法连接服务器

    redis通过6379端口无法连接服务器看了网上很多解决方案,都是端口问题,将127.0.0.1改为0.0.0.0,就ok了,但是本人的问题不是端口问题,端口本来就是0.0.0.0。其实redis无法连接数据库就只有这几种可能,防火墙,安全组,密码,绑定IP。这次的问题感觉还是比较让人费解的,当更换端口号的时候就可以,默认端口6379就是不行。按照顺序依次检查了防火墙,安全组,密码,配置文件,都确认没有问题之后,再次启动redis,…

  • aorg.gjt.mm.mysql.Driver和com.mysql.jdbc.Driver的关系

    aorg.gjt.mm.mysql.Driver和com.mysql.jdbc.Driver的关系今天写项目时,发现mysql驱动是 aorg.gjt.mm.mysql.Driver ,以前还真的没有见过,真是见识太少,于是记录一下。 com.mysql.jdbc.Driver的前身是org.gjt.mm.mysql.Driver,现在主要用com.mysql.jdbc.Driver,但为了保持兼容性保留了org.gjt.mm.mysql.Driver这个路径的引用。…

  • mybatis-plus 在线激活码_在线激活

    (mybatis-plus 在线激活码)本文适用于JetBrains家族所有ide,包括IntelliJidea,phpstorm,webstorm,pycharm,datagrip等。IntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,下面是详细链接哦~https://javaforall.cn/100143.html…

  • memorycleaner汉化版(v4l2 userptr)

    本文链接:https://blog.csdn.net/coroutines/article/details/70141086可参考:http://www.it610.com/article/4522348.htm//v4l2官方翻译基于V4L2的应用,通常面临着大块数据的读取与拷贝等问题。尤其在嵌入式系统中,对于实时性能要求较高的应用,拷贝会花上几十个ms…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号