PostgreSQL建表语法

本文学习一下Postgres中的表，涉及建表语句，临时表等。

标准建表语句

在PG中支持标准的建表语法CREATE TABLE。示例如下：

create table changan(a int, b int);  -- 建表语句

除上面的建表方式外，还可以以其他表为模板来创建新表，示例如下：

create table sl03(like changan); 

postgres@postgres=# \d changan
              Table "public.changan"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

postgres@postgres=# \d sl03
                Table "public.sl03"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

但是上面的方式没有将源表的约束复制过来，可用INCLUDING ALL把所有的属性全部复制过去：

create table sl03(like changan including all); 

临时表

PG支持两种类型临时表，一种是会话级临时表，一种是事务级临时表。在会话级别的临时表中，数据可以一直保存在整个会话的声明周期中，而在事务级别的临时表中，数据只存在于这个事务的生命周期中。

在PG中，不管是事务级的临时表还是会话级的临时表，当会话结束时都会消失，这与Oracle数据库不同，在Oracle数据库中，只是临时表中的数据消失，而临时表还在。

需要注意的是如果在两个不同的session中创建一个同名的临时表，实际上创建的是两种不同的表。

-- 创建会话级临时表
postgres@postgres=# create temporary table sl03(a int, b int);
CREATE TABLE
postgres@postgres=# \d sl03
              Table "pg_temp_4.sl03"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

可以看出，临时表是生成一个特殊的Schema下的表，这个Schema名为pg_temp_xx，其中的xx代表一个数字。

创建事务级临时表，可通过ON COMMIT DROP来实现：

begin;
create temp table sl03(a int, b int) on commit drop;  -- 创建事务级临时表
insert into sl03 values(1,1);
select * from sl03;
commit;
select * from sl03  -- fail, 事务级临时表已不存在

ON COMMIT DROP：表示数据只存在于事务周期中，事务提交后临时表就消失了。

UNLOGGED表

其含义如图名字，通过禁止产生WAL日志的方式提升写性能。因为没有WAL日志，所以表的内容无法在主备库直接同步，如果此时数据库异常宕机，表的内容将消失，所以可以把UNLOGGED表称为“半临时表”。

create unlogged table sl03(a int, b int);

postgres@postgres=# \d sl03
           Unlogged table "public.sl03"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          |