PostgreSQL中比较冷门的特性（一）

作者：瀚高PG实验室 （Highgo PG Lab）- Kalath

PostgreSQL包含大量优异的特性。他们中很大一部分是众所周知的，但也有一些极其好用的特性没有得到广泛的关注。本文主要介绍五个最建议使用的PostgreSQL特性。

一、listen与notify
PostgreSQL附带一个简单的发布-订阅通知系统。
可以向正在侦听该主题的所有已连接订户广播特定主题的消息。 消息由Postgres服务器推送到侦听客户端。 不需要轮询，但数据库驱动程序应支持异步向应用程序传递通知。指令格式为：

NOTIFY channel [ ,’ payload’ ]

notify命令会发送一个通知事件，同时可以带一个可选的消息信息字符串到每个客户端应用程序，这些应用程序已经为当前数据库的指定名称通道预先执行了“LISTEN channel”命令。如果上面的命令没有指定消息信息字符串，则消息信息字符串是空字符串。
举个例子：

postgres=# LISTEN postgres;
LISTEN
postgres=# NOTIFY postgres,'hello world';
NOTIFY
Asynchronous notification "postgres" with payload "hello world" received from server process with PID 2429.

可以收到一个异步消息
二、表继承（Table Inheritance）

表继承是PostgreSQL中特有的。

举个例子，创建一张表people，然后再创建一张表stuff，它继承people表。

postgres=# CREATE TABLE people(name text, age int, sex boolean);
CREATE TABLE
postgres=# CREATE TABLE stuff(id int)INHERITS(people);
CREATE TABLE

向stuff表中插入三条数据：

postgres=# INSERT INTO stuff VALUES('zhang',19,true,1);
INSERT 0 1
postgres=# INSERT INTO stuff VALUES('wang',14,false,2);
INSERT 0 1
postgres=# INSERT INTO stuff VALUES('lee',23,true,3);
INSERT 0 1

此时，查询people表，发现可以查到插入到stuff表中的数据：

postgres=# SELECT * FROM people;
 name  | age | sex 
-------+-----+-----
 zhang |  19 | t
 wang  |  14 | f
 lee   |  23 | t
(3 rows)

但如果向people表中插入一条数据，查询stuff表则看不到这条数据：

postgres=# INSERT INTO people VALUES('tang',30,true);
INSERT 0 1
postgres=# SELECT * FROM people;
 name  | age | sex 
-------+-----+-----
 tang  |  30 | t
 zhang |  19 | t
 wang  |  14 | f
 lee   |  23 | t
(4 rows)

postgres=# SELECT * FROM stuff;
 name  | age | sex | id 
-------+-----+-----+----
 zhang |  19 | t   |  1
 wang  |  14 | f   |  2
 lee   |  23 | t   |  3
(3 rows)

所以可以总结出：查询父表时，会把这个父表中子表的数据也查询出来，反之则不行。
如果只想把父表本身的数据查询出来，需要在查询的表名前加ONLY：

postgres=# SELECT * FROM ONLY people;
 name | age | sex 
------+-----+-----
 tang |  30 | t
(1 row)

所有父表的检查约束和非空约束都会自动被所有子表继承。不过其他类型的约束不被继承。
一个子表可从多个父表继承，对父表的REINDEX和VACUUM命令不会影响到子表。

三、外部数据包装器（Foreign Data Wrappers）
使用FDW，就可以拥有实际提供来自另一个PostgreSQL实例的数据的虚拟表。它提供了一种标准化的方法来访问和操作存储在您连接的PostgreSQL服务器外部的数据。 有各种可用的FDW实现，可以连接到各种不同的数据源。 这些通常情况下会被打包为扩展。
标准的PostgreSQL发行版附带postgres_fdw扩展，它允许连接到其他PostgreSQL服务器。 例如，可以将一个大表移动到另一个服务器，并在本地设置一个虚拟表。

四、分区表（Partitioned Tables）
PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。当表的大小超过了数据库服务器的物理内存大小时应该使用分区表。在使用继承实现的分区表时，一般会让父表为空，数据都存储在子表中。
从版本10开始，PostgreSQL本身支持基于从一个或多个列值计算的分区键值将表分片到多个子表中。 这允许将单个大型表物理存储为单独的表，以实现更好的DML性能和存储管理。
举个例子说明创建分区表的步骤。首先创建父表：

postgres=# CREATE TABLE matches(match_num int NOT NULL, match_day date NOT NULL)PARTITION BY RANGE(match_day);
CREATE TABLE

然后分别创建三个分区表，分区依据是按照月份：

postgres=# CREATE TABLE matches_201801 PARTITION OF matches FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE
postgres=# CREATE TABLE matches_201802 PARTITION OF matches FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
CREATE TABLE
postgres=# CREATE TABLE matches_201803 PARTITION OF matches FOR VALUES FROM ('2018-03-01') TO ('2018-04-01');
CREATE TABLE

子表必须手动创建。可以在父级别查询和插入，PostgreSQL会将其路由到相应的子表：

postgres=# INSERT INTO matches VALUES (878178,'2018-01-25');
INSERT 0 1
postgres=# INSERT INTO matches VALUES (512716,'2018-02-03');
INSERT 0 1
postgres=# INSERT INTO matches VALUES (281901,'2018-03-12');
INSERT 0 1

然后，查询子表，看到记录已经被写入子表中：

postgres=# SELECT * FROM matches_201801;
 match_num | match_day  
-----------+------------
    878178 | 2018-01-25
(1 row)

postgres=# SELECT * FROM matches_201802;
 match_num | match_day  
-----------+------------
    512716 | 2018-02-03
(1 row)

postgres=# SELECT * FROM matches_201803;
 match_num | match_day  
-----------+------------
    281901 | 2018-03-12
(1 row)

查询父表会返回合并的结果：

postgres=# SELECT * FROM matches;
 match_num | match_day  
-----------+------------
    878178 | 2018-01-25
    512716 | 2018-02-03
    281901 | 2018-03-12
(3 rows)

五、Range数据类型
PostgreSQL特有的数据类型，此类型可进行范围快速搜索。
PostgreSQL9.2版本之后开始提供的，用于表示范围。PostgreSQL中已经内置了一些常用的Range类型，不需要创建就能直接使用，例如：int4range表示4字节整数的范围类型，int8range表示8字节大整数的范围类型等等。

举例说明：

首先创建一张weights表：

postgres=# CREATE TABLE weights(item text, weight int4range);
CREATE TABLE

向表中插入数据：

postgres=# INSERT INTO weights VALUES('cup','[2,5)');
INSERT 0 1
postgres=# INSERT INTO weights VALUES('desk','[30,45)');
INSERT 0 1
postgres=# INSERT INTO weights VALUES('chair','[15,25)');
INSERT 0 1

可以看到插入weight列的值都为一个区间。

现在，可以查询weight值在（10,50）之间的记录：

postgres=# SELECT * FROM weights WHERE weight && int4range(10,50);
 item  | weight  
-------+---------
 desk  | [30,45)
 chair | [15,25)
(2 rows)