专栏名称: 唤之
目录
相关文章推荐
码农翻身  ·  2月，软考彻底爆了！！ ·  20 小时前  
OSC开源社区  ·  谈谈DeepSeek-R1满血版推理部署和优化 ·  2 天前  
程序猿  ·  模仿一下领导说话的样子 ·  3 天前  
程序员的那些事  ·  游戏开发到一半，程序员突然坐牢去了... ·  4 天前  
程序员的那些事  ·  微信听劝，这个扰人功能可以关了 ·  3 天前  
51好读  ›  专栏  ›  唤之

PostgreSQL 的一些你可能不知道但应该尝试的功能

唤之  · 掘金  · 程序员  · 2018-07-05 10:44

正文

PostgreSQL包含许多重要的功能。他们中的许多人都非常知名。其他人可以是非常有用的，但没有广泛赞赏。以下是我们首选的PostgreSQL功能，您可能没有仔细看过，但实际上应该这样做，因为它们可以帮助您更快地将代码投入生产，使操作更轻松，并且通常可以使用更少的代码和劳动来完成任务。

发布/订阅通知

PostgreSQL带有一个简单的非持久基于主题的发布 - 订阅通知系统。它不是Kafka，但功能确实支持常见用例。

关于特定主题的消息可以广播给正在监听该主题的所有连接的订阅者。这些消息被   Postgres服务器 推 送给侦听客户端。轮询不是必需的，但您的数据库驱动程序应支持异步向应用程序传递通知。

通知由主题名称和有效负载组成（最多约8000个字符）。有效载荷通常是一个JSON字符串，但它当然可以是任何东西。您可以使用 NOTIFY 命令发送通知  ： 

NOTIFY'foo_events'，'{“userid”：42，“action”：“grok”}'

或者 pg_notify（） 函数： 

SELECT pg_notify（'foo_events'，'{“userid”：42，“action”：“grok”}'）;

订阅发生在 LISTEN 命令中，但通常您必须使用驱动程序特定的API。这里的 围棋版本 的例子。

ljwheyxy ljwheyxy 翻译于 1周前 1人顶 顶 翻译得不错哦！ 其它翻译版本(1)

表继承

假如有一张叫 “invoices（发票）” 的表。你现在想支持 “government invoices（政府发票）”，这种发票在原来的发票之上添加了一些字段。该如何建模？是在 invoices 表中添加若干可空字段，还是增加一个可空的 JSON 字段？不妨试试继承功能： 

CREATE TABLE invoices (
    invoice_number   int  NOT NULL PRIMARY KEY,
    issued_on        date NOT NULL DEFAULT now()
);

CREATE TABLE government_invoices (
    department_id    text NOT NULL
) INHERITS (invoices);

上述模型反映出了政府发票就是发票，但比发票多一些属性的情况。上面的 “government_invoices” 表总共有 3 列： 

test=# \d invoices
                  Table "public.invoices "
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
Indexes:
    "invoices_pkey " PRIMARY KEY, btree (invoice_number)
Number of child tables: 1 (Use \d+ to list them.)

test=# \d government_invoices
            Table "public.government_invoices "
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 invoice_number | integer |           | not null |
 issued_on      | date    |           | not null | now()
 department_id  | text    |           | not null |
Inherits: invoices

为它添加数据行就跟独立表一样： 

INSERT INTO invoices (invoice_number) VALUES (100);

INSERT INTO government_invoices
    (invoice_number, department_id) VALUES (101, 'DOD');

不过观察一下 SELECT 时的情况： 

test=# SELECT * FROM government_invoices;
 invoice_number | issued_on  | department_id
----------------+------------+---------------
            101 | 2018-06-19 | DOD
(1 row)

test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
            100 | 2018-06-19
            101 | 2018-06-19
(2 rows)

子表添加的编号为 101 的发票，也父表中也列出来了。这样做的好处是在父表中进行的各种算法In完全以忽略子表的存在。

从 这个文档 可以了解到更多关于 PostgreSQL 继承方面的内容。

边城 边城 翻译于 1周前 1人顶 顶 翻译得不错哦！ 其它翻译版本(1)

外部数据包装器

你知道你可以有一张虚表用来指向另一个PostgreSQL实例吗？或者另一个SQLite、MongoDB、Redis甚至其它的数据库？这个功能叫做外部数据包装器(FDW)，它提供一个标准化的方法来存取和操作连接到Postgres服务器的外部数据源。有各种各样的FDW实现让你可以连接到不同的数据源，它们通常被打包为扩展插件。

标准Postgres分发包中有一个postgres_fdw扩展，它可以让你连接到其它Postgres服务器。例如，你可以移动一张大表到其它服务器，同时在本地建立一张虚表(正确的术语叫做"外部表 "): 

-- install the extension (required only once)
CREATE EXTENSION postgres_fdw;

-- big_server is our big, remote server with the migrated table
CREATE SERVER big_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.1.2.3', dbname 'big_db');

-- create a user mapping for the app user
CREATE USER MAPPING FOR app_user SERVER big_server
  OPTIONS (user 'remote_user', password 'remote_pass');

-- and finally create the virtual table invoices -> big_db.public.invoices
CREATE FOREIGN TABLE invoices (
  invoice_num int NOT NULL PRIMARY KEY
  -- other columns omitted for brevity
) SERVER big_server;

这个 Wiki 有一个很好的列表列出了许多FDW的有效实现。

除了可以从其它服务器存取数据，FDW也被用作实现交互存储层，比如 cstore_fdw .

还有一个 dblink 扩展，它是另一种用来存取远程PostgreSQL数据的实现.

xiaoaiwhc1 xiaoaiwhc1 翻译于 1周前 0人顶 顶 翻译得不错哦！

拆分表

从版本 10 开始，PostgreSQL 原生支持将一个表拆分成多个子表，其拆分基于对一列或多列数据的计算来进行。这一功能可以让一个巨大的表在物理上存储于多个表中，改善DML性能和存储管理。

下面演示了如何创建拆分表，该演示会为每个月的数据增加一张表： 

-- the parent table
CREATE TABLE invoices (
    invoice_number   int  NOT NULL,
    issued_on        date NOT NULL DEFAULT now()
) PARTITION BY RANGE (issued_on);
  
-- table for the month of May 2018
CREATE TABLE invoices_2018_05 PARTITION OF invoices
  FOR VALUES FROM ('2018-05-01') TO ('2018-06-01');

-- table for the month of June 2018
CREATE TABLE invoices_2018_06 PARTITION OF invoices
  FOR VALUES FROM ('2018-06-01') TO ('2018-07-01');

子表必须由人工或通过程序创建，这个创建过程不会自动发生。

你可以在父级表中查询或插入数据，PostgreSQL 会自动到子表中去进行操作，来看一下：

先插入两行数据： 

test=# INSERT INTO invoices VALUES (10042, '2018-05-15');
INSERT 0 1
test=# INSERT INTO invoices VALUES (43029, '2018-06-15');
INSERT 0 1

可以看到数据实际被插入到了子表中： 

test=# SELECT * FROM invoices_2018_05;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
(1 row)

test=# SELECT * FROM invoices_2018_06;
 invoice_number | issued_on
----------------+------------
          43029 | 2018-06-15
(1 row)

但在父表中也可以完成查询，返回合并的结果： 

test=# SELECT * FROM invoices;
 invoice_number | issued_on
----------------+------------
          10042 | 2018-05-15
          43029 | 2018-06-15
(2 rows)

拆分方法与继承相似（在父表级别查询），但也存在一些区别（比如在拆分父表中没有保存数据）。你可以在 这个文档 中阅读到更多相关内容。

已经进入 Beta 阶段的 PostgreSQL 11 对这一功能会有所改进， 这篇文章 对此进行了叙述。

边城 边城 翻译于 1周前 0人顶 顶 翻译得不错哦！ 其它翻译版本(1)

区间类型

你以前与温度范围、日程表、价格区间或类似的数值范围打过交道吗？如果是，那你就会有这样的经验：看似简单的问题总会导致你抓耳挠腮并且经常深夜调试bug。以下是一个包含区间列的表和一些数值： 

CREATE TABLE prices (
    item  text,
    price int4range -- int4range is a range of regular integers
);

INSERT INTO prices VALUES ('mouse',    '[10,16)');
INSERT INTO prices VALUES ('keyboard', '[20,31)');
INSERT INTO prices VALUES ('joystick', '[35,56)');

在错配方括号中的数值代表半开区间。以下是一个查询语句，它可以找出在价格区间15$~30$中的所有项，使用了&&操作符(区间交错): 

test=# SELECT * FROM prices WHERE price && int4range(15,30);
   item   |  price
----------+---------
 mouse    | [10,16)
 keyboard | [20,31)
(2 rows)

为了让你印象深刻，你可以尝试一下使用无区间类型的查询语句有多难(试试就好)。

区间类型非常强大 --- 这里还有 操作符 、 函数 ，你也可以定义你自己的区间类型，甚至还可以索引它们。

为了学习更多关于区间的知识，你可以看看 这篇文章 ，还有 这篇 。

xiaoaiwhc1 xiaoaiwhc1 翻译于 1周前 0人顶 顶 翻译得不错哦！ 其它翻译版本(1)

数组类型

PostgreSQL很久以前就已经支持数组类型了。数组类型可以精简应用代码并可以简化查询操作。以下是一个在表中使用数组列的例子： 

CREATE TABLE posts (
    title text NOT NULL PRIMARY KEY,
    tags  text[]
);

假设每一行代表一篇博客，每篇博客又都有一个标签集，下面是我们如何列出所有带“postgres”和"go "标签的博客的代码： 

test=# SELECT title, tags FROM posts WHERE '{"postgres ", "go "}' <@ tags;
               title               |          tags
-----------------------------------+------------------------
 Writing PostgreSQL Triggers in Go | {postgres,triggers,go}
(1 row)

这里数组类型的使用使我们的数据模型更精确，同时也简化了查询操作。Postgres数组总是与 操作符和函数 一起出现，其中也包括 集合函数 。你也可以基于数组表达式创建索引。 这里 有一篇关于如何在Go语言中使用数组的文章。

xiaoaiwhc1 xiaoaiwhc1 翻译于 1周前 0人顶 顶 翻译得不错哦！

触发器

当对表中的行进行插入、更新或删除操作时，你能请求PostgreSQL执行一个特殊的函数，这个函数甚至可以在插入过程中修改值。你可以点击 这里 了解更多关于触发器的信息。以下是一个例子：当创建用户时，触发器发出通知并写入稽核日志。 

-- a table of users
CREATE TABLE users (
  username text NOT NULL PRIMARY KEY
);

-- an audit log
CREATE TABLE audit_log (
  at          timestamptz NOT NULL DEFAULT now(),
  description text NOT NULL
);

-- the actual function that is executed per insert
CREATE FUNCTION on_user_added() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    -- add an entry into the audit log
    INSERT INTO audit_log (description)
        VALUES ('new user created, username is ' || NEW.username);
    -- send a notification
    PERFORM pg_notify('usercreated', NEW.username);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- set the function as an insert trigger
CREATE TRIGGER on_user_added AFTER INSERT ON users
  FOR EACH ROW EXECUTE PROCEDURE on_user_added();







请到「今天看啥」查看全文


推荐文章
码农翻身  ·  2月，软考彻底爆了！！
20 小时前
OSC开源社区  ·  谈谈DeepSeek-R1满血版推理部署和优化
2 天前
程序猿  ·  模仿一下领导说话的样子
3 天前
程序员的那些事  ·  游戏开发到一半，程序员突然坐牢去了...
4 天前
程序员的那些事  ·  微信听劝，这个扰人功能可以关了
3 天前
果壳  ·  女友胃难受，只会说“多喝热水”？看别人家的男友是怎么做的
8 年前
考研研学姐  ·  考研路上“时间管理”的七大招
8 年前
上海发布  ·  【提示】上海市网信办依法约谈熊猫直播、全民直播
7 年前
英国那些事儿  ·  英国菜难吃？我来教你啃！
7 年前
新芽NewSeed  ·  有了Uber的前车之鉴，爱bi（彼）才会赢的Airbnb能打破入华魔咒吗？
7 年前
Sov5搜索   ·   小百科   ·   今天看啥   ·   移动版
51好读 - 好文章就要读起来!