1、创建表

--创建有多个主键的表时
postgres=# create table t1(id1 int,id2 int,note varchar(20),CONSTRAINT pk_t1 primary key(id1,id2));
CREATE TABLE

--以其他表为模板创建新表，例：以表t1为模板创建t2表
postgres=# create table t2(LIKE t1);
CREATE TABLE

--上例创建的表没有把源表列上的约束复制过来。如果想完全复制源表列上的约束和其他信息，需要添加“INCLUDING”关键字
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
INCLUDING STORAGE
INCLUDING COMMENTS
INCLUDING ALL

--可以使用create table ... as 的语句来创建表
postgres=# create table t3 as select * from t1 with no data;
CREATE TABLE AS
复制代码

2、表的存储属性

TOAST技术：The Oversized-attribute Storage Technique。用于存储大字段的值。由于PostgreSQL页面的大小是固定的，通常为8k，且不允许跨越多个页面，所以不能存储非常大的字段值。所以大的字段通常被压缩或切片成多个物理行存到另一张系统表中。即TOAST表。

3、临时表

使用临时表的目的：

存储中间结果集，查询起来更快

临时表的应用场景：

• 执行复杂sql时，借助临时表，提高速度和效率
• 写存储过程时，可能需要连接到多张表，此时可以将多个表多成几个部分，每个部分的结果得到个临时表，最后将操作临时表得到结果集，降低同时连接的消耗
• 在导出数据时，只想导出表中部分数据，就可以形成临时表，之后清理临时表

Postgres分为会话级临时表及事务级临时表。一个数据保存在整个会话的生命周期，一个数据只存在于这个事务的生命周期。（在oracle中，会话结束后，临时表中的数据消失，而临时表还存在;pg会话结束，数据和表都小时）如果两个session同时创建一个同名的临时表，实际创建的是两张不同的表（其他session加上模式"模式名一般为：pg_temp_xx"可以查看临时表但不能访问）。

--创建临时表，默认为会话级（退出会话后，数据删除，"on commit preserve rows"）
postgres=# create temporary table tmp_t1(id int primary key,note text);
CREATE TABLE

--创建事务级临时表(数据只存在事务周期，事务提交后数据就消失)
postgres=# create temporary table temp_t2(id int primary key,note text) on commit delete rows;
CREATE TABLE

--创建事务级临时表(数据只存在事务周期，事务提交后临时表就消失)
postgres=# create temp table tmp_t3(id int primary key,note text) on commit drop;
CREATE TABLE
复制代码

4、UNLOGGED表

从postgresql9.1后新增的一种表，主要通过禁止产生wal日志提升写性能。由于不写日志，所以在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但如果是正常关闭，unlogged表的内容不会丢失。

postgres=# create unlogged table unlogged_t1(id int primary key,t text);
CREATE TABLE
复制代码

5、默认值

Postgres支持给表添加默认值，例：

postgres=# create table t10(a int, b text default now());
CREATE TABLE
postgres=# insert into t10 values(1);
INSERT 0 1
postgres=# select * from t10;
 a |               b
---+-------------------------------
 1 | 2021-12-12 23:53:37.684805+08
(1 row)

--注：没有声明默认值的情况下，默认值为NULL
复制代码

6、约束

约束有如下几种：

• 检查约束
• 非空约束
• 唯一约束
• 主键约束
• 外键约束

注：同一个字段如果有多个约束，一个接一个写

--检查约束，下例中第一种称为字段约束，第二种为表约束，第三种为约束命名
create table t1(a int ,b int check(b>10));
create table t2(a int,b int,check(b>10));
create table t4(a int,b int constraint b_check check(b>10));
​
--非空约束
postgres=# create table t6(a int,b int not null);
CREATE TABLE
postgres=# insert into t6 values(1,null);
ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (1, null).
​
--唯一约束
postgres=# create table t_unique(a int unique);
CREATE TABLE
postgres=# insert into t_unique values(1);
INSERT 0 1
postgres=# insert into t_unique values(1);
ERROR:  duplicate key value violates unique constraint "t_unique_a_key"
DETAIL:  Key (a)=(1) already exists.
​
--主键约束，主键约束和唯一约束唯一的区别为主键不能为空
postgres=# create table t_primary(a int primary key);
CREATE TABLE
postgres=# insert into t_primary values(1);
INSERT 0 1
postgres=# insert into t_primary values(1);
ERROR:  duplicate key value violates unique constraint "t_primary_pkey"
DETAIL:  Key (a)=(1) already exists.
postgres=# insert into t_primary values(null);
ERROR:  null value in column "a" violates not-null constraint
DETAIL:  Failing row contains (null).
​
--外键约束，外键约束是对表之间关系的一种约束。用于约束本表中的一个或多个字段的数值必须出现在另一个表的一个或多个字段中。如下示例表示person表中的sex字段的值必须为sex表中sex字段的值
create table sex(sex text primary key);
create table person(name text,sex text references sex(sex));
复制代码

7、修改表

--创建表
postgres=# create table test(id int,age int, name text);
CREATE TABLE
​
--增加字段，对于表中已经存在的行来说，最初会给新增的字段填充默认值，如果没有默认值，则填充null
postgres=# alter table test add column sex text;
ALTER TABLE
​
--删除字段，该字段相关的约束一并删除，若存在外键约束，则需要使用cascade关键字
postgres=# alter table test drop column sex;
ALTER TABLE
​
--增加约束
postgres=# alter table test add check (age>18);
ALTER TABLE
​
--特别的，增加非空约束
postgres=# alter table test alter column id set not null;
ALTER TABLE
​
--删除约束，删除约束需要知道约束的名字
postgres=# alter table test drop constraint test_age_check;
ALTER TABLE
​
--特别的，非空约束没有名字，需要使用如下语法删除
postgres=# alter table test alter column id drop not null;
ALTER TABLE
​
--修改默认值，不能使用双引号
postgres=# alter table test alter column name set default 'postgres';
ALTER TABLE
​
--删除默认值
postgres=# alter table test alter column name drop default;
ALTER TABLE
​
--修改字段数据类型
postgres=# alter table test alter column name type varchar(10);
ALTER TABLE
​
--重命名字段
postgres=# alter table test rename column name to thing;
ALTER TABLE
​
--重命名表
alter table test rename to test1;
复制代码

8、表继承

--创建父表
postgres=# create table persons(name text,age int,sex boolean);
CREATE TABLE
​
--创建子表，继承父表
postgres=# create table students(class_no int) inherits (persons);
CREATE TABLE
​
--当查询父表时会把父表中子表的数据查询出来，反之则不行。
--父表更新数据子表无法查看，子表更新数据父表可以查看
--如果只想把父表本身的数据查询出来，只需要在查询的表名前加only关键字
postgres=# select * from only persons;
​
--所有父表的检查约束和非空约束都会自动被所有子表继承，其他约束不会被继承。一个子表可以从多个父表继承，它将拥有所有父表字段的总和。如果同一个字段出现在多个父表或者同时出现在父表和子表的定义中。字段会“融合”。因此在子表里就只有一个这样的字段。
复制代码

9、通过表继承实现分区表

1）什么是表分区？

将逻辑上的一个大表分割成物理上的几块。

2）表分区的好处？

• 使删除历史数据更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区即可，如果没有分区，通过DELETE删除历史数据时会很慢，还容易导致vacuum超载
• 某些类型的查询性能得到极大的提升。特别当访问率较高的行位于一个单独的分区或者少数几个分区上的情况下。
• 当查询或更新一个分区的大部分记录时，连续扫描该分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升。
• 使用分区表可以将不同分区安置在不同的物理介质上。很少用到的历史数据可以使用表空间的技术移动到便宜一些的慢速存储介质上。

3）什么时候使用分区表？

基本原则：当表的大小超过了数据库服务器的物理内存时使用

4）建分区表的步骤

0. 创建父表，所有分区都从它继承，该表无数据，且最好不要在上面定义检查约束。另外，在其上定义索引或者唯一约束也无意义。
1. 创建几个子表，每个都是从主表上继承。通常这些表不会增加任何字段，子表被称为分区。
2. 给分区表增加约束，定义每个分区允许的键值。
3. 对于每个分区，在关键字字段上创建一个索引。
4. 定义一个规则或者触发器，把对主表的数据插入重定向到合适的分区表中。
5. 确保constraint_exclusion参数是打开的。

分区表是使用触发器来把插入的数据重新定位到相应的分区

5）分区的优化技巧

打开约束排除（constraint_exclusion），在postgresql9.2.4中，该参数的默认值为partition。采用默认值，在sql查询中将where语句的过滤条件与表上的check条件进行对比，可以得到不需要扫描的分区。从而跳过这些不需要访问的分区表，从而使性能得到提升 。如果该参数设置为off，则会扫描每张分区子表。

6）声明式分区

声明式分区即使用相应的DDL语句来创建分区表。而不是用表继承的方式创建分区表。声明式分区更简单。

--创建分区表
postgres=# create table t1(a int, b text) partition by range(a);
CREATE TABLE

--创建分区
postgres=# create table t1_1 partition of t1 for values from (0) to (3) ;
CREATE TABLE
postgres=# create table t1_2 partition of t1 for values from (4) to (7) ;
CREATE TABLE

--插入数据
postgres=# insert into t1 values(0,now());
INSERT 0 1
postgres=# insert into t1 values(1,now());
INSERT 0 1
postgres=# insert into t1 values(2,now());
INSERT 0 1
postgres=# insert into t1 values(3,now());
ERROR:  no partition of relation "t1" found for row
DETAIL:  Partition key of the failing row contains (a) = (3).
postgres=# insert into t1 values(4,now());
INSERT 0 1

--查询
postgres=# select * from t1_1 ;
 a |               b
---+-------------------------------
 0 | 2021-12-14 07:57:34.879489+08
 1 | 2021-12-14 07:57:38.226515+08
 2 | 2021-12-14 07:57:40.720481+08
(3 rows)

postgres=# select * from t1_2;
 a |               b
---+-------------------------------
 4 | 2021-12-14 07:57:49.293821+08
(1 row)
复制代码

参考《PostgreSQL修炼之道 从小工到专家 第2版》