PostgreSQL 临时表[通俗易懂]

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

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

转载自：
http://blog.163.com/digoal@126/blog/static/1638770402012101575032326/

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;

----

(1 row)

test1的数据事务提交后数据已删除.

digoal=> select * from test1;

----

(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