postgresql临时表

PostgreSQL中的临时表分两种，一种是会话级临时表，一种是事务级临时表。

在会话级临时表中，数据可以存在于整个会话的生命周期中，

在事务级临时表中，数据只能存在于事务的生命周期中。

不管是会话级还是事务级的临时表，当会话结束后，临时表会消失，这和Oracle数据库不同。Oracle数据库当会话结束后，数据消失，而表依然存在。

方法一：创建临时表，再批量导入数据

---创建临时表
CREATE TEMPORARY TABLE temp_table (
  id int,
    name varchar(50),
    age int,
)ON COMMIT PRESERVE ROWS;
---验证临时表（无数据）
SELECT * FROM temp_table;
---批量导入数据
INSERT INTO temp_table(
 id,
 name,
 age
)
SELECT
    id,
    name,
    age
FROM target_table;

这种方法不实用，因为临时表每个字段都需要自己定义，比较费时费力。

方法二：直接从结果集创建临时表

---根据查询的结果集生成临时表不需要每个字段都定义
CREATE TEMPORARY TABLE temp_table AS (SELECT * FROM target_table);
---验证临时表
SELECT * FROM temp_table;

关键字TEMPORARY可以直接用简写temp，这种方法更简洁，更实用

1、会话级临时表，其实上面方法二用的就是会话级临时表

2、事务级临时表，在创建语句中需要加上"on commit delete rows"子句。

##创建事务级临时表
test=# create TEMPORARY table tmp2 (id int primary key,note text) on commit delete rows;
CREATE TABLE

##开始一个事务
test=# begin;
BEGIN

##插入测试数据
test=# insert into tmp2 values (1,'Tom');
INSERT 0 1
test=# insert into tmp2 values (2,'Peter');
INSERT 0 1

##查看表中数据
test=# select * from tmp2;
 id | note 

  1 | Tom
  2 | Peter
(2 rows)

##结束事务
test=# end;
COMMIT

##再次查看，表中数据已经消失，因为事务级临时表中数据只存在于事务的生命周期中
test=# select * from tmp2;
 id | note 

(0 rows)

(1)ON COMMIT PRESERVE ROWS 表示临时表的数据在事务结束后保留;
(2)ON COMMIT DELETE ROWS      表示临时表的数据在事务结束后truncate掉;
(3)ON COMMIT DROP                    表示临时表在事务结束后删除。

使用示例：（通常用来保存临时数据，用于加快数据查询速度）

drop table if  exists  hour_temp;
create temptable hour_temp AS (SELECT * FROM hour where datatime>now() + '- 6 hour');
临时表调用的sql