首页

PostgreSQL 的特色 SQL 语法

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

参考文献: Regina Obe Leo Hsu 著, 丁奇鹏 译《PostgreSQL即学即用》

目录

视图简介

视图简介

在关系型数据库中, 如果需要反复执行某个复杂的查询（有可能涉及多个表的关联查询）时, 可以考虑创建一个视图. 当然, 视图也可以基于单个表.

创建视图的语句是

CREATE OR REPLACE VIEW view_name AS
SELECT ... FROM ... WHERE ...

物化视图

9.3 版中引入了物化视图. 物化视图的意思是将视图逻辑映射后的数据记录实际存储下来, 这样访问物化视图时就省略了视图底层 SQL 的执行过程, 就像访问一张本地表一样.

一旦物化视图建立好以后, 只有对它执行 REFRESH 操作时才会再次从基础表中读取数据.

• 物化视图的优点是, 可以节省计算资源, 因为视图底层 SQL（这种SQL逻辑可能极其复杂）不用反复执行.
• 缺点是如果刷新不及时, 则取出的数据可能不是最新的.

单表视图

单表视图

最简单的视图是从单个表得出的. 如果打算将数据写回到该表, 请始终包含主键.

例. 创建基于单表的视图.

CREATE OR REPLACE VIEW census.vw_facts_2011 AS
SELECT fact_type_id, val, yr, tract_id 
FROM census.facts WHERE yr=2011;

postgres@localhost:5436 postgresql_book# CREATE OR REPLACE VIEW census.vw_facts_2011 AS
postgresql_book-# SELECT fact_type_id, val, yr, tract_id
postgresql_book-# FROM census.facts WHERE yr=2011;
CREATE VIEW
时间：111.111 ms

使用命令 \dv census.* 或 \dv census. 查看 census 架构下的视图列表.

postgres@localhost:5436 postgresql_book# \dv census.*
                   关联列表
 架构模式 |     名称      |  型别  |  拥有者
----------+---------------+--------+----------
 census   | vw_facts_2011 | 视观表 | postgres
(1 行记录)

查看该视图, 由于总共有100504条记录, 故只列出前10条.

postgres@localhost:5436 postgresql_book# select * from census.vw_facts_2011 limit 10;
 fact_type_id |   val    |  yr  |  tract_id
--------------+----------+------+-------------
            1 | 2359.000 | 2011 | 25001010400
            1 | 2985.000 | 2011 | 25001010500
            1 | 4494.000 | 2011 | 25001010100
            1 | 4305.000 | 2011 | 25001010206
            1 | 3077.000 | 2011 | 25001010208
            1 | 2706.000 | 2011 | 25001010304
            1 | 3254.000 | 2011 | 25001010306
            1 | 2998.000 | 2011 | 25001010600
            1 | 4345.000 | 2011 | 25001010700
            1 | 3788.000 | 2011 | 25001010800
(10 行记录)

自从 9.3 版起, 就可以使用 INSERT、UPDATE 和 DELETE 命令在该视图中更改数据了. 更新和删除命令将遵从作为视图一部分的任何 WHERE 条件.

下面的删除命令将仅删除 yr=2011 的记录. (在做实验前请做好数据备份, 或者先检查哪些记录是要被删除的, 先进行确认.)

DELETE FROM census.vw_facts_2011 WHERE val=0;

事实上, 符合这个条件的记录有很多, 如下. 删除前要小心.

postgres@localhost:5436 postgresql_book# select count(*) from census.vw_facts_2011 where val=0;
 count
-------
 21438
(1 行记录)

我们以 tutorial 数据库中的 ipAddr 表做实验. 建立一个视图, 列出以 112 开头的所有 IP 地址, 即形如 112.*.*.* 的地址.

注意 112 的二进制为1110000, 而 116 的二进制为1110100, 因此如果使用下面的语句

SELECT id, ip FROM ipAddr WHERE (ip & inet '112.0.0.0')= inet '112.0.0.0';

则会列出很多数据, 不一定以 112 开头.

postgres@localhost:5436 tutorial# SELECT id, ip FROM ipAddr WHERE (ip & inet '112.0.0.0')= inet '112.0.0.0';
 id |       ip
----+-----------------
 13 | 112.234.3.188
 14 | 112.32.190.194
 15 | 113.44.107.118
 16 | 116.132.138.202
 17 | 116.132.252.174
 18 | 116.132.254.187
 19 | 116.132.254.94
 20 | 116.179.32.210
 21 | 116.179.32.239
 22 | 116.179.37.110
 23 | 116.179.37.163
 24 | 116.179.37.178
 25 | 116.179.37.18
 26 | 116.179.37.233
 27 | 116.179.37.93
 28 | 116.179.37.95
 29 | 116.204.108.183
 30 | 116.204.18.41
 31 | 116.204.36.10
 32 | 116.204.37.75
 33 | 116.204.39.178
 34 | 116.204.42.110
 35 | 116.204.8.240
 36 | 117.140.42.223
 37 | 117.179.39.210
 38 | 120.219.174.53
 39 | 121.37.100.105
 40 | 121.37.100.32
 41 | 121.37.110.36
 42 | 121.37.99.207
 43 | 121.78.21.42
 44 | 121.78.21.49
 45 | 123.182.49.201
 46 | 123.182.50.100
 47 | 123.182.51.82
 48 | 124.239.12.16
 49 | 125.113.228.230
 50 | 125.45.56.102
 51 | 125.69.200.2
(39 行记录)

正确的做法是

CREATE OR REPLACE VIEW vw_ipAddr AS
SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '112.0.0.0';

postgres@localhost:5436 tutorial# SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '112.0.0.0';
 id |       ip
----+----------------
 13 | 112.234.3.188
 14 | 112.32.190.194
(2 行记录)

建立视图并查看.

postgres@localhost:5436 tutorial# CREATE OR REPLACE VIEW vw_ipAddr AS
tutorial-# SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '112.0.0.0';
CREATE VIEW
时间：52.380 ms
postgres@localhost:5436 tutorial# \dv
                       关联列表
 架构模式 |          名称          |  型别  |  拥有者
----------+------------------------+--------+----------
 public   | vw_ipaddr              | 视观表 | postgres
 public   | vw_subscribers_current | 视观表 | postgres
(2 行记录)

postgres@localhost:5436 tutorial# select * from vw_ipaddr;
 id |       ip
----+----------------
 13 | 112.234.3.188
 14 | 112.32.190.194
(2 行记录)

如果要更改视图的定义, 比如要列出以116开头的 IP 地址, 则执行下面的语句.

CREATE OR REPLACE VIEW vw_ipAddr AS
SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '116.0.0.0';

postgres@localhost:5436 tutorial# CREATE OR REPLACE VIEW vw_ipAddr AS
tutorial-# SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '116.0.0.0';
CREATE VIEW
时间：15.546 ms
postgres@localhost:5436 tutorial# select * from vw_ipAddr;
 id |       ip
----+-----------------
 16 | 116.132.138.202
 17 | 116.132.252.174
 18 | 116.132.254.187
 19 | 116.132.254.94
 20 | 116.179.32.210
 21 | 116.179.32.239
 22 | 116.179.37.110
 23 | 116.179.37.163
 24 | 116.179.37.178
 25 | 116.179.37.18
 26 | 116.179.37.233
 27 | 116.179.37.93
 28 | 116.179.37.95
 29 | 116.204.108.183
 30 | 116.204.18.41
 31 | 116.204.36.10
 32 | 116.204.37.75
 33 | 116.204.39.178
 34 | 116.204.42.110
 35 | 116.204.8.240
(20 行记录)

现在删除视图中形如 *.179.*.* 的记录.

DELETE FROM vw_ipAddr WHERE (ip & inet '0.255.0.0')= inet '0.179.0.0';

postgres@localhost:5436 tutorial# DELETE FROM vw_ipAddr WHERE (ip & inet '0.255.0.0')= inet '0.179.0.0';
DELETE 9
时间：12.697 ms

然后查看视图中是否还有形如 *.179.*.* 的IP地址.

postgres@localhost:5436 tutorial# SELECT * FROM vw_ipAddr WHERE (ip & inet '0.255.0.0')= inet '0.179.0.0';
 id | ip
----+----
(0 行记录)

注意! 原基础表 ipAddr 中的记录也会被删除.

postgres@localhost:5436 tutorial# SELECT * FROM ipAddr WHERE (ip & inet '0.255.0.0')= inet '0.179.0.0';
 id |       ip
----+----------------
 37 | 117.179.39.210
(1 行记录)

这里只剩一条的原因是, 117.179.39.210 一开始并不在视图中, 它不是以 116 开头.

练习, 将之前删除的9条记录恢复到ipAddr表中, 且保持原来的 id(即第20至28).

ALTER SEQUENCE ipaddr_id_seq RESTART WITH 20;
INSERT INTO ipaddr (ip) VALUES ('116.179.32.210'),('116.179.32.239'),('116.179.37.110'),('116.179.37.163'),('116.179.37.178'),('116.179.37.18'),('116.179.37.233'),('116.179.37.93'),('116.179.37.95');
ALTER SEQUENCE ipaddr_id_seq RESTART WITH 140;

我们修改一下视图的定义, 加上 WITH CHECK OPTION 这个选项. 该选项是 9.4 版引入的. 创建视图如果包含此修饰符, 则此视图中插入的数据或者更新后的数据如果落在视图可见范围之外, 系统会报错.

CREATE OR REPLACE VIEW vw_ipAddr AS
SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '116.0.0.0'
WITH CHECK OPTION;

postgres@localhost:5436 tutorial# CREATE OR REPLACE VIEW vw_ipAddr AS
tutorial-# SELECT id, ip FROM ipAddr WHERE (ip & inet '255.0.0.0')= inet '116.0.0.0'
tutorial-# WITH CHECK OPTION;
CREATE VIEW
时间：10.569 ms

尝试执行下面的操作.

UPDATE vw_ipAddr SET ip=inet '117.179.32.210'  WHERE ip= inet '116.179.32.210';

postgres@localhost:5436 tutorial# UPDATE vw_ipAddr SET ip=inet '117.179.32.210'  WHERE ip= inet '116.179.32.210';
ERROR:  new row violates WITH CHECK OPTION for view "vw_ipaddr"
描述:  Failing row contains (20, 117.179.32.210).
时间：12.674 ms

上面的错误表明, 更新后的行违反了视图 vw_ipaddr 的 Check 约束.

使用触发器来更新视图

使用触发器来更新视图

视图可以将针对多张表的关联查询封装为针对视图的简单查询.

如果视图的基表有多张, 那么直接更新视图是不允许的. 因为多张表必然带来的问题就是操作要落到哪个基表上, PostgreSQL 是无法自动判定的.

此时可以通过编写触发器来对这些操作进行转义处理.

我们首先建立一个关联了两张表的视图.

CREATE OR REPLACE VIEW census.vw_facts AS
SELECT y.fact_type_id, y.category, y.fact_subcats, y.short_name, x.tract_id, x.yr, x.val, x.perc
FROM census.facts As x INNER JOIN census.lu_fact_types As y
ON x.fact_type_id = y.fact_type_id;

postgres@localhost:5436 postgresql_book# CREATE OR REPLACE VIEW census.vw_facts AS
postgresql_book-# SELECT y.fact_type_id, y.category, y.fact_subcats, y.short_name, x.tract_id, x.yr, x.val, x.perc
postgresql_book-# FROM census.facts As x INNER JOIN census.lu_fact_types As y
postgresql_book-# ON x.fact_type_id = y.fact_type_id;
CREATE VIEW
时间：30.116 ms
postgres@localhost:5436 postgresql_book# \dv census.
                   关联列表
 架构模式 |     名称      |  型别  |  拥有者
----------+---------------+--------+----------
 census   | vw_facts      | 视观表 | postgres
 census   | vw_facts_2011 | 视观表 | postgres
(2 行记录)

然后可以定义一个或多个 INSTEAD OF 触发器来实现针对 INSERT、DELETE、UPDATE 这三大基本操作的转义处理.

触发器需要有一个基础函数, 你可以使用任何语言来编写该基础函数, 起命名也没有规则限制.

下面使用 PL/pgSQL 语法来编写.

CREATE OR REPLACE FUNCTION census.trig_vw_facts_ins_upd_del()
RETURNS trigger AS
$$
BEGIN
	IF (TG_OP = 'DELETE') THEN -- 对删除操作进行转义处理, 筛选条件的字段取值来源于 OLD 记录.
		DELETE FROM census.facts AS f
		WHERE
			f.tract_id = OLD.tract_id AND f.yr = OLD.yr AND
			f.fact_type_id = OLD.fact_type_id;
		RETURN OLD;
	END IF;

	IF (TG_OP = 'INSERT') THEN -- 对插入操作进行转义处理.
		INSERT INTO census.facts(tract_id, yr, fact_type_id, val, perc)
		SELECT NEW.tract_id, NEW.yr, NEW.fact_type_id, NEW.val, NEW.perc;
		RETURN NEW;
	END IF;

	IF (TG_OP = 'UPDATE') THEN -- 对更新操作进行转义处理. 根据 OLD 记录的内容判定哪些记录要更新为 NEW 记录.
		IF
			ROW(OLD.fact_type_id, OLD.tract_id, OLD.yr, OLD.val, OLD.perc) !=
			ROW(NEW.fact_type_id, NEW.tract_id, NEW.yr, NEW.val, NEW.perc)
		THEN -- 比较OLD记录和NEW记录的字段值, 只有二者不一样时才真正执行更新动作.
			UPDATE census.facts AS f
			SET 
				tract_id = NEW.tract_id,
				yr = NEW.yr,
				fact_type_id = NEW.fact_type_id,
				val = NEW.val,
				perc = NEW.perc
			WHERE
				f.tract_id = OLD.tract_id AND
				f.yr = OLD.yr AND
				f.fact_type_id = OLD.fact_type_id;
			RETURN NEW;
		ELSE
			RETURN NULL;
		END IF;
	END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;

• OLD 记录是指原始的针对视图的待删除或更新前的视图记录, 即这里的 OLD 记录非视图基础表的记录.
• NEW 记录是指原始的针对视图的更新后或待插入的视图记录, 即这里的 NEW 记录非视图基础表的记录.

postgres@localhost:5436 postgresql_book# \df census.
                       函数列表
 架构模式 | 名称 | 结果资料型别 | 参数资料型别 | 型别
----------+------+--------------+--------------+------
(0 行记录)

postgres@localhost:5436 postgresql_book# CREATE OR REPLACE FUNCTION census.trig_vw_facts_ins_upd_del()
postgresql_book-# RETURNS trigger AS
postgresql_book-# $$
postgresql_book$# BEGIN
postgresql_book$#       IF (TG_OP = 'DELETE') THEN -- 对删除操作进行转义处理, 筛选条件的字段取值来源于 OLD 记录.
postgresql_book$#               DELETE FROM census.facts AS f
postgresql_book$#               WHERE
postgresql_book$#                       f.tract_id = OLD.tract_id AND f.yr = OLD.yr AND
postgresql_book$#                       f.fact_type_id = OLD.fact_type_id;
postgresql_book$#               RETURN OLD;
postgresql_book$#       END IF;
postgresql_book$#
postgresql_book$#       IF (TG_OP = 'INSERT') THEN -- 对插入操作进行转义处理.
postgresql_book$#               INSERT INTO census.facts(tract_id, yr, fact_type_id, val, perc)
postgresql_book$#               SELECT NEW.tract_id, NEW.yr, NEW.fact_type_id, NEW.val, NEW.perc;
postgresql_book$#               RETURN NEW;
postgresql_book$#       END IF;
postgresql_book$#
postgresql_book$#       IF (TG_OP = 'UPDATE') THEN -- 对更新操作进行转义处理. 根据 OLD 记录的内容判定哪些记录要更新为 NEW 记录.
postgresql_book$#               IF
postgresql_book$#                       ROW(OLD.fact_type_id, OLD.tract_id, OLD.yr, OLD.val, OLD.perc) !=
postgresql_book$#                       ROW(NEW.fact_type_id, NEW.tract_id, NEW.yr, NEW.val, NEW.perc)
postgresql_book$#               THEN -- 比较OLD记录和NEW记录的字段值, 只有二者不一样时才真正执行更新动作.
postgresql_book$#                       UPDATE census.facts AS f
postgresql_book$#                       SET
postgresql_book$#                               tract_id = NEW.tract_id,
postgresql_book$#                               yr = NEW.yr,
postgresql_book$#                               fact_type_id = NEW.fact_type_id,
postgresql_book$#                               val = NEW.val,
postgresql_book$#                               perc = NEW.perc
postgresql_book$#                       WHERE
postgresql_book$#                               f.tract_id = OLD.tract_id AND
postgresql_book$#                               f.yr = OLD.yr AND
postgresql_book$#                               f.fact_type_id = OLD.fact_type_id;
postgresql_book$#                       RETURN NEW;
postgresql_book$#               ELSE
postgresql_book$#                       RETURN NULL;
postgresql_book$#               END IF;
postgresql_book$#       END IF;
postgresql_book$# END;
postgresql_book$# $$
postgresql_book-# LANGUAGE plpgsql VOLATILE;
CREATE FUNCTION
时间：38.683 ms

postgres@localhost:5436 postgresql_book# \df census.
                                  函数列表
 架构模式 |           名称            | 结果资料型别 | 参数资料型别 |  型别
----------+---------------------------+--------------+--------------+--------
 census   | trig_vw_facts_ins_upd_del | trigger      |              | 触发器
(1 行记录)

使用命令 \sf functionName可以显示函数的定义.

\sf census.trig_vw_facts_ins_upd_del

接下来, 我们将此触发器函数绑定到视图上, 语法如下

CREATE TRIGGER trig_01_vw_facts_ins_upd_del
INSTEAD OF INSERT OR UPDATE OR DELETE ON census.vw_facts
FOR EACH ROW 
EXECUTE PROCEDURE census.trig_vw_facts_ins_upd_del();

注意不能在触发器名字前加上 "census."

postgres@localhost:5436 postgresql_book# \dy census.
               事件触发器列表
 名称 | Event | 拥有者 | 使能 | 过程 | 标签
------+-------+--------+------+------+------
(0 行记录)

postgres@localhost:5436 postgresql_book# CREATE TRIGGER trig_01_vw_facts_ins_upd_del
postgresql_book-# INSTEAD OF INSERT OR UPDATE OR DELETE ON census.vw_facts
postgresql_book-# FOR EACH ROW
postgresql_book-# EXECUTE PROCEDURE census.trig_vw_facts_ins_upd_del();
CREATE TRIGGER
时间：22.604 ms

查看触发器的几个方法

select * from pg_trigger;

postgres@localhost:5436 postgresql_book# select * from pg_trigger;
 tgrelid |            tgname            | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
---------+------------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------
  155780 | trig_01_vw_facts_ins_upd_del | 155784 |     93 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     | NULL
(1 行记录)

select tgrelid::regclass, tgname from pg_trigger;

postgres@localhost:5436 postgresql_book# select tgrelid::regclass, tgname from pg_trigger;
     tgrelid     |            tgname
-----------------+------------------------------
 census.vw_facts | trig_01_vw_facts_ins_upd_del
(1 行记录)

或者从 information_schema.triggers 中查询.

SELECT
    trigger_schema,
    trigger_name,
    event_object_table
FROM 
    information_schema.triggers
ORDER BY 
    event_object_table;

postgres@localhost:5436 postgresql_book# SELECT
postgresql_book-#     trigger_schema,
postgresql_book-#     trigger_name,
postgresql_book-#     event_object_table
postgresql_book-# FROM
postgresql_book-#     information_schema.triggers
postgresql_book-# ORDER BY
postgresql_book-#     event_object_table;
 trigger_schema |         trigger_name         | event_object_table
----------------+------------------------------+--------------------
 census         | trig_01_vw_facts_ins_upd_del | vw_facts
 census         | trig_01_vw_facts_ins_upd_del | vw_facts
 census         | trig_01_vw_facts_ins_upd_del | vw_facts
(3 行记录)

根据触发器名称获取id

postgres@localhost:5436 postgresql_book# select tgrelid from pg_trigger where tgname='trig_01_vw_facts_ins_upd_del';
 tgrelid
---------
  155780
(1 行记录)

根据oid获取表的名称.

postgres@localhost:5436 postgresql_book# select oid,relname  from pg_class where oid=155780;
  oid   | relname
--------+----------
 155780 | vw_facts
(1 行记录)

列出表的信息.

postgres@localhost:5436 postgresql_book# \d census.vw_facts
             视观表 "census.vw_facts"
     栏位     |           型别           | 修饰词
--------------+--------------------------+--------
 fact_type_id | integer                  |
 category     | character varying(100)   |
 fact_subcats | character varying(255)[] |
 short_name   | character varying(50)    |
 tract_id     | character varying(11)    |
 yr           | integer                  |
 val          | numeric(12,3)            |
 perc         | numeric(6,2)             |
触发器：
    trig_01_vw_facts_ins_upd_del INSTEAD OF INSERT OR DELETE OR UPDATE ON census.vw_facts FOR EACH ROW EXECUTE PROCEDURE census.trig_vw_facts_ins_upd_del()

参考自

https://database.guide/2-ways-to-list-all-triggers-in-a-postgresql-database/

https://stackoverflow.com/questions/6898453/show-the-code-of-a-function-procedure-and-trigger-in-postgresql

物化视图

物化视图

物化视图特性是从 9.3 版开始支持的.

物化视图会把视图可见范围内的数据在本地缓存下来, 然后就可以当成一张本地表来使用.

首次创建物化视图以及对其执行 REFRESH MATERIALIZED VIEW 刷新操作时都会触发数据缓存动作, 只不过前者是全量缓存, 后者是增量刷新.

物化视图最典型的应用场景是用于加速时效性要求不高的长时复杂查询. 在 OLAP (On-Line Analytical Processing)联机分析处理（或在线分析与处理）领域, 这种查询是经常出现的.

物化视图支持建立索引以加快查询速度.

例

建立物化视图 census.vw_facts_2011_materialized

CREATE MATERIALIZED VIEW census.vw_facts_2011_materialized AS
SELECT fact_type_id, val, yr, tract_id FROM census.facts WHERE yr = 2011;

postgres@localhost:5436 postgresql_book# CREATE MATERIALIZED VIEW census.vw_facts_2011_materialized AS
postgresql_book-# SELECT fact_type_id, val, yr, tract_id FROM census.facts WHERE yr = 2011;
SELECT 100504
时间：91.385 ms

使用 \dm 命令列出物化视图列表.

postgres@localhost:5436 postgresql_book# \dm census.
                          关联列表
 架构模式 |            名称            |   型别   |  拥有者
----------+----------------------------+----------+----------
 census   | vw_facts_2011_materialized | 物化视图 | postgres
(1 行记录)

然后对物化视图建立一个索引, 语法与在普通表上建索引完全相同.

CREATE UNIQUE INDEX ix
ON census.vw_facts_2011_materialized (tract_id, fact_type_id, yr);

postgres@localhost:5436 postgresql_book# CREATE UNIQUE INDEX ix
postgresql_book-# ON census.vw_facts_2011_materialized (tract_id, fact_type_id, yr);
CREATE INDEX
时间：139.222 ms

postgres@localhost:5436 postgresql_book# \d census.vw_facts_2011_materialized
 物化视图 "census.vw_facts_2011_materialized"
     栏位     |         型别          | 修饰词
--------------+-----------------------+--------
 fact_type_id | integer               |
 val          | numeric(12,3)         |
 yr           | integer               |
 tract_id     | character varying(11) |
索引：
    "ix" UNIQUE, btree (tract_id, fact_type_id, yr)

当物化视图中含大量记录时, 为了加快对它的访问速度, 我们需要对数据进行排序. 要实现这一点, 最简单的办法就是在物化视图时使用的 SELECT 语句中增加 ORDER BY 子句.

另外一种方法就是对其执行聚簇排序操作以使得记录的物理存储顺序与索引的顺序相同. 具体的步骤是, 首先建一个索引, 该索引应体现你所希望的排序; 然后基于指定索引对物化视图执行 CLUSTER 命令. 语法是

CLUSTER census.vw_facts_2011_materialized USING ix;
CLUSTER census.vw_facts_2011_materialized;

第一句是指定聚簇操作所依据的索引名. 执行过以后系统就会自动记下该表是依据哪个索引进行聚簇排序的, 后面再次执行聚簇操作时系统会自动使用该索引, 所以索引名仅在首次聚簇操作时需要, 后续不再需要.

每次刷新过物化视图后, 都需要重新对其进行一次聚簇排序操作.

postgres@localhost:5436 postgresql_book# CLUSTER census.vw_facts_2011_materialized USING ix;
CLUSTER
时间：209.541 ms
postgres@localhost:5436 postgresql_book# CLUSTER census.vw_facts_2011_materialized;
CLUSTER
时间：121.664 ms

相对于 CLUSTER 方案来说, ORDER BY 方案的优点在于每次执行 REFRESH MATERIALIZED VIEW 时都会自动对记录进行重排序, 但 CLUSTER 方案必须手动执行; 其缺点在于物化视图加了 ORDER BY 以后, REFRESH 操作执行会耗时更久.

在正式使用 ORDER BY 方案之前, 你应该对 REFRESH 操作进行测试, 看性能是否可接受. 另一种测试方法是直接运行创建物化视图时所用的带 ORDER BY 的 SQL 语句.

在 PostgreSQL 9.3 版中, 刷新物化视图的语法如下:

REFRESH MATERIALIZED VIEW census.vw_facts_2011_materialized;

在 PostgreSQL 9.4 版中, 为了解决物化视图刷新操作导致的锁表问题, 可以使用以下语法:

REFRESH MATERIALIZED VIEW CONCURRENTLY census.vw_facts_2011_materialized;

物化视图有以下几个缺点.

• 不支持通过 CREATE OR REPLACE 语法来对一个现有的物化视图进行重建. 要想重建只能先删除再创建, 即使只做很小的改变也需要这么干.
• 删除语法是 DROP MATERIALIZED VIEW view_name; 删除后, 该视图上的所有的索引都会丢失.
• 每次刷新数据时都要执行一次 REFRESH MATERIALIZED VIEW 操作. PostgreSQL 不支持自动刷新物化视图. 要想实现自动刷新, 你必须使用类似 crontab、pgAgent 定时任务或者是触发器之类的机制.
• 在 9.3 版中, 刷新物化视图是一个阻塞操作; 也就是说, 视图在刷新期间是无法访问的. 9.4 版中引入了一个新的 CONCURRENTLY 关键字, 在 REFRESH 命令中增加了该关键字以后, 可以解除锁定限制, 前提条件是被刷新的物化视图上要有一个唯一索引. 实现无锁刷新的代价就是如果有人在刷新期间进行视图访问, 那么刷新时间会变长.

PostgreSQL的专有SQL语法

灵活易用的PostgreSQL的专有SQL语法

本节中介绍的这些SQL语法都是PostgreSQL专有的. “专有”意味着该语法不符合 ANSI SQL 标准, 不适合移植到其他数据库管理系统.

DISTINCT ON

DISTINCT ON 功能类似于 DISTINCT 但却可以精确到更细的粒度. DISTINCT 会将结果集中完全重复的记录剔除, 但 DISTINCT ON 可以将结果集中指定字段值的重复记录剔除.

具体实现方法是先对结果集按照 DISTINCT ON 指定的字段进行排序, 然后筛选出每个字段值第一次出现时所在的记录, 其余的记录都剔除.

下面的例子演示了如何获取马萨诸塞州每个县的第一个人口统计区的信息.

SELECT DISTINCT ON (left(tract_id, 5))
  left(tract_id, 5) As county, tract_id, tract_name
FROM census.lu_tracts
ORDER BY county, tract_id;

postgres@localhost:5436 postgresql_book# SELECT DISTINCT ON (left(tract_id, 5))
postgresql_book-#   left(tract_id, 5) As county, tract_id, tract_name
postgresql_book-# FROM census.lu_tracts
postgresql_book-# ORDER BY county, tract_id;
 county |  tract_id   |                      tract_name
--------+-------------+-------------------------------------------------------
 25001  | 25001010100 | Census Tract 101, Barnstable County, Massachusetts
 25003  | 25003900100 | Census Tract 9001, Berkshire County, Massachusetts
 25005  | 25005600100 | Census Tract 6001, Bristol County, Massachusetts
 25007  | 25007200100 | Census Tract 2001, Dukes County, Massachusetts
 25009  | 25009201100 | Census Tract 2011, Essex County, Massachusetts
 25011  | 25011040100 | Census Tract 401, Franklin County, Massachusetts
 25013  | 25013800101 | Census Tract 8001.01, Hampden County, Massachusetts
 25015  | 25015820101 | Census Tract 8201.01, Hampshire County, Massachusetts
 25017  | 25017300100 | Census Tract 3001, Middlesex County, Massachusetts
 25019  | 25019950100 | Census Tract 9501, Nantucket County, Massachusetts
 25021  | 25021400100 | Census Tract 4001, Norfolk County, Massachusetts
 25023  | 25023500101 | Census Tract 5001.01, Plymouth County, Massachusetts
 25025  | 25025000100 | Census Tract 1, Suffolk County, Massachusetts
 25027  | 25027700100 | Census Tract 7001, Worcester County, Massachusetts
(14 行记录)

可以尝试执行下面的语句.

SELECT DISTINCT left(tract_id, 5) As county, tract_id, tract_name FROM census.lu_tracts ORDER BY county, tract_id;

请注意, ON 修饰符支持设置多列, 运算时将基于这多个列的总体唯一性来进行去重操作. 同时查询语句中 ORDER BY 子句的排序字段列表的最左侧必须是 DISTINCT ON 指定的字段列表, 即保证整个结果集是按照这几个字段排序的, 这样最终去重后得到的结果才是你想要的.

LIMIT 和 OFFSET 关键字

LIMIT 关键字指定了查询时仅返回指定数量的记录, OFFSET 关键字指定了从第几条记录开始返回. 你可以将二者结合起来使用也可以单独使用.

一般来说, 这两个关键字总是和 ORDER BY 联用.

如果不设置 OFFSET, 则其默认值为 0.

示例. 前面例子所得查询结果集有14条, 现在返回从第3条开始的3条记录. 此时 OFFSET 要设置为 2.

SELECT DISTINCT ON (left(tract_id, 5))
  left(tract_id, 5) As county, tract_id, tract_name
FROM census.lu_tracts
ORDER BY county, tract_id LIMIT 3 OFFSET 2;

postgres@localhost:5436 postgresql_book# SELECT DISTINCT ON (left(tract_id, 5))
postgresql_book-#   left(tract_id, 5) As county, tract_id, tract_name
postgresql_book-# FROM census.lu_tracts
postgresql_book-# ORDER BY county, tract_id LIMIT 3 OFFSET 2;
 county |  tract_id   |                    tract_name
--------+-------------+--------------------------------------------------
 25005  | 25005600100 | Census Tract 6001, Bristol County, Massachusetts
 25007  | 25007200100 | Census Tract 2001, Dukes County, Massachusetts
 25009  | 25009201100 | Census Tract 2011, Essex County, Massachusetts
(3 行记录)

简化的类型转换语法

ANSI SQL 标准中定义了一个名为 CAST 的类型转换函数, 可以实现数据类型之间的互转.

例如 CAST('2011-1-11' AS date) 可以将文本 2011-1-11 转换为一个日期型数据.

PostgreSQL 支持一种简写语法, 该语法使用了两个冒号来表示转换关系, 具体格式为:

'2011-1-11'::date

这种写法形式更简洁也更易于使用, 而且可以执行级联操作, 即执行多个类型转换操作. 例如:

someXML::text::integer

先将 someXML 转换为 text 类型, 然后再转换为 integer 类型.

一次性插入多条记录

PostgreSQL 支持一次性插入多条记录的语法.

INSERT INTO logs_2025 (user_name, description, log_ts)
VALUES
  ('robe', 'logged in', '2025-11-20 10:15 AM EST'),
  ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'),
  ('mike', 'logged in', '2025-11-22 09:25 AM EST');

注意这里 VALUES 不能使用 VALUE, 即使插入单个记录也不能使用VALUE. 这与 MySQL 不一样.

postgres@localhost:5436 tutorial# INSERT INTO logs_2025 (user_name, description, log_ts)
tutorial-# VALUES
tutorial-#   ('robe', 'logged in', '2025-11-20 10:15 AM EST'),
tutorial-#   ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'),
tutorial-#   ('mike', 'logged in', '2025-11-22 09:25 AM EST');
INSERT 0 3
时间：26.419 ms
postgres@localhost:5436 tutorial# select * from logs_2025;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      1 | robe      | logged in   | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out  | 2025-11-21 23:20:00+08
      3 | mike      | logged in   | 2025-11-22 22:25:00+08
(3 行记录)

请注意, 在 PostgreSQL 中 VALUES 子句并不是只能作为 INSERT 语句的一部分来使用, 它其实是一个动态生成的临时结果集, 可用于多种场合. 例如可以跟在 FROM 后面作为临时表.

SELECT L.user_name, L.log_ts, L.description
FROM (
  VALUES
  ('robe', 'logged in', '2025-11-20 10:15 AM EST'),
  ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'),
  ('mike', 'logged in', '2025-11-22 09:25 AM EST')
) AS L (user_name, description, log_ts);

postgres@localhost:5436 tutorial# SELECT L.user_name, L.log_ts, L.description
tutorial-# FROM (
tutorial(#   VALUES
tutorial(#   ('robe', 'logged in', '2025-11-20 10:15 AM EST'),
tutorial(#   ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'),
tutorial(#   ('mike', 'logged in', '2025-11-22 09:25 AM EST')
tutorial(# ) AS L (user_name, description, log_ts);
 user_name |         log_ts          | description
-----------+-------------------------+-------------
 robe      | 2025-11-20 10:15 AM EST | logged in
 lhsu      | 2025-11-21 10:20 AM EST | logged out
 mike      | 2025-11-22 09:25 AM EST | logged in
(3 行记录)

从上面看到, 将VALUES 子句当作一个虚拟表使用时, 需要为该表指定字段名. 并且可以看到 log_ts 无法进行隐式转换类型（即自动执行转换类型）. 此时需要将那些字段值手动执行类型转换.

SELECT L.user_name, L.log_ts, L.description
FROM (
  VALUES
  ('robe', 'logged in', '2025-11-20 10:15 AM EST'::timestamptz),
  ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'::timestamptz),
  ('mike', 'logged in', '2025-11-22 09:25 AM EST'::timestamptz)
) AS L (user_name, description, log_ts);

postgres@localhost:5436 tutorial# SELECT L.user_name, L.log_ts, L.description
tutorial-# FROM (
tutorial(#   VALUES
tutorial(#   ('robe', 'logged in', '2025-11-20 10:15 AM EST'::timestamptz),
tutorial(#   ('lhsu', 'logged out', '2025-11-21 10:20 AM EST'::timestamptz),
tutorial(#   ('mike', 'logged in', '2025-11-22 09:25 AM EST'::timestamptz)
tutorial(# ) AS L (user_name, description, log_ts);
 user_name |         log_ts         | description
-----------+------------------------+-------------
 robe      | 2025-11-20 23:15:00+08 | logged in
 lhsu      | 2025-11-21 23:20:00+08 | logged out
 mike      | 2025-11-22 22:25:00+08 | logged in
(3 行记录)

使用 LIKE 实现不区分大小写的查询

PostgreSQL 内部的操作(查询、函数、运算符等)默认是区分大小写的. 但有时在进行文本搜索时不需要区分大小写, 此时有两种实现办法.

一种是将 ANSI LIKE 运算符两边的文本都用 upper 函数转为大写, 但这样会导致用不上索引, 或者必须单独建立一个基于 upper 函数的函数索引才能使用查询语句用上索引;

另一种是使用 PostgreSQL 所特有的 ILIKE 运算符(~), 前面已经碰到过. 语法如下:

SELECT tract_name FROM census.lu_tracts WHERE tract_name ILIKE '%duke%';

postgres@localhost:5436 tutorial# \c postgresql_book
您现在已经连线到数据库 "postgresql_book",用户 "postgres".
postgres@localhost:5436 postgresql_book# SELECT tract_name FROM census.lu_tracts WHERE tract_name ILIKE '%duke%';
                   tract_name
------------------------------------------------
 Census Tract 2001, Dukes County, Massachusetts
 Census Tract 2002, Dukes County, Massachusetts
 Census Tract 2003, Dukes County, Massachusetts
 Census Tract 2004, Dukes County, Massachusetts
 Census Tract 9900, Dukes County, Massachusetts
(5 行记录)

可以返回结果集的函数

PostgreSQL 允许返回集的函数显示在 SQL 语句的 SELECT 子句中.

在一个复杂的 SQL 语句中使用返回结果集的函数很容易就会导致意外的结果, 这是因为这类函数输出的结果集会与该语句其他部分生成的结果集产生笛卡尔积, 从而生成更多的记录行.

CREATE TABLE interval_periods (i_type interval);
INSERT INTO interval_periods (i_type)
VALUES ('5 months'), ('132 days'), ('4862 hours');

postgres@localhost:5436 postgresql_book# \c tutorial
您现在已经连线到数据库 "tutorial",用户 "postgres".
postgres@localhost:5436 tutorial# CREATE TABLE interval_periods (i_type interval);
CREATE TABLE
时间：69.767 ms
postgres@localhost:5436 tutorial# INSERT INTO interval_periods (i_type)
tutorial-# VALUES ('5 months'), ('132 days'), ('4862 hours');
INSERT 0 3
时间：12.913 ms
postgres@localhost:5436 tutorial# select * from interval_periods;
   i_type
------------
 5 mons
 132 days
 4862:00:00
(3 行记录)

SELECT i_type,
  generate_series('2025-11-01'::date, '2025-12-31'::date, i_type) As dt
FROM interval_periods;

postgres@localhost:5436 tutorial# SELECT i_type,
tutorial-#   generate_series('2025-11-01'::date, '2025-12-31'::date, i_type) As dt
tutorial-# FROM interval_periods;
   i_type   |           dt
------------+------------------------
 5 mons     | 2025-11-01 00:00:00+08
 132 days   | 2025-11-01 00:00:00+08
 4862:00:00 | 2025-11-01 00:00:00+08
(3 行记录)

如果第一个参数改为 '2025-01-01'::date

SELECT i_type,
  generate_series('2025-01-01'::date, '2025-12-31'::date, i_type) As dt
FROM interval_periods;

postgres@localhost:5436 tutorial# SELECT i_type,
tutorial-#   generate_series('2025-01-01'::date, '2025-12-31'::date, i_type) As dt
tutorial-# FROM interval_periods;
   i_type   |           dt
------------+------------------------
 5 mons     | 2025-01-01 00:00:00+08
 5 mons     | 2025-06-01 00:00:00+08
 5 mons     | 2025-11-01 00:00:00+08
 132 days   | 2025-01-01 00:00:00+08
 132 days   | 2025-05-13 00:00:00+08
 132 days   | 2025-09-22 00:00:00+08
 4862:00:00 | 2025-01-01 00:00:00+08
 4862:00:00 | 2025-07-22 14:00:00+08
(8 行记录)

限制对继承表的 DELETE、UPDATE、INSERT操作的影响范围

如果表间是继承关系, 那么查询父表时就会将子表中满足条件的记录也查出来. DELETE 和 UPDATE 操作也遵循类似逻辑, 即对父表的修改操作也会影响子表的记录.

PostgreSQL 提供了 ONLY 关键字, 可限定操作仅限于主表范围之内而使子表不受波及.

之前创建的 logs_2025 是 logs 的子表.

postgres@localhost:5436 tutorial# \d+ logs
                                                 资料表 "public.logs"
    栏位     |           型别           |                     修饰词                     |   存储   | 统计目标 | 描述
-------------+--------------------------+------------------------------------------------+----------+----------+------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass) | plain    |          |
 user_name   | character varying(50)    |                                                | extended |          |
 description | text                     |                                                | extended |          |
 log_ts      | timestamp with time zone | 非空 缺省 now()                                | plain    |          |
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_2025_log_ts" btree (log_ts)
    "idx_logs_log_ts" btree (log_ts)
检查约束限制
    "chk" CHECK (user_name::text = lower(user_name::text))
子表: logs_2025

postgres@localhost:5436 tutorial# select * from logs;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      1 | robe      | logged in   | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out  | 2025-11-21 23:20:00+08
      3 | mike      | logged in   | 2025-11-22 22:25:00+08
(3 行记录)

postgres@localhost:5436 tutorial# insert into logs (user_name, description, log_ts) values('hfxu', 'logged in', '2025-11-28 12:59 pm');
INSERT 0 1
时间：11.916 ms

postgres@localhost:5436 tutorial# select * from logs;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      4 | hfxu      | logged in   | 2025-11-28 12:59:00+08
      1 | robe      | logged in   | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out  | 2025-11-21 23:20:00+08
      3 | mike      | logged in   | 2025-11-22 22:25:00+08
(4 行记录)

期中 log_id 为 1,2,3 的三条记录实际上属于子表 logs_2025.

postgres@localhost:5436 tutorial# select * from only logs;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      4 | hfxu      | logged in   | 2025-11-28 12:59:00+08
(1 行记录)


时间：1.098 ms
postgres@localhost:5436 tutorial# select * from only logs_2025;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      1 | robe      | logged in   | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out  | 2025-11-21 23:20:00+08
      3 | mike      | logged in   | 2025-11-22 22:25:00+08
(3 行记录)

DELETE USING 语法

我们经常会遇到“只有当记录的字段值落在另外一个结果集中时才需要删除该记录”的情形, 那么此时就必须借助一次关联查询才能定位到要删除的目标记录.

USING 子句可以将需要借助的一个或者多个中间表（或者子查询）纳入同一个 DELETE 语句中.

DELETE FROM census.facts
USING census.lu_fact_types As ft
WHERE facts.fact_type_id = ft.fact_type_id AND ft.short_name = 's01';

postgres@localhost:5436 postgresql_book# DELETE FROM census.facts
postgresql_book-# USING census.lu_fact_types As ft
postgresql_book-# WHERE facts.fact_type_id = ft.fact_type_id AND ft.short_name = 's01';
DELETE 1478
时间：112.653 ms

显示删除了 1478 条记录. (Exer. 恢复该数据.)

符合标准的方式将会是, 在 WHERE 子句中使用 IN 表达式.

将修改影响到的记录行返回给用户

RETURNING 是 ANSI SQL 规定的标准语法. 下面的例子中, 我们通过 RETURNING 子句将 DELETE 操作中被删除的记录返回给了用户. 当然, INSERT 和 UPDATE 操作也是可以使用 RETURNING 子句的.

对于带 serial 类型字段的表来说, RETURNING 语法是很有用的, 因为向这类表中插入记录时, serial 字段时临时生成而非用户指定的. 也就是说在插入动作完成之前, 用户也不指定 serial 字段的值会是多少, 除非是再查询一遍. 而 RETURNING 语法使得用户不用再次查询就立即得到了 serial 字段的值.

最常用的用法一般是 RETURNING *, 即返回所有字段的值, 但也可以指定仅返回特定字段.

SELECT * FROM census.lu_fact_types AS f
WHERE f.fact_subcats[3]='Hispanic or Latino:' AND f.fact_subcats[4] > '';

postgres@localhost:5436 postgresql_book# SELECT * FROM census.lu_fact_types AS f
postgresql_book-# WHERE f.fact_subcats[3]='Hispanic or Latino:' AND f.fact_subcats[4] > '';
 fact_type_id |  category  |                                      fact_subcats                                      | short_name
--------------+------------+----------------------------------------------------------------------------------------+------------
           62 | Population | {D011,Total:,"Hispanic or Latino:","White alone"}                                      | d011
           63 | Population | {D012,Total:,"Hispanic or Latino:","Black or African American alone"}                  | d012
           64 | Population | {D013,Total:,"Hispanic or Latino:","American Indian and Alaska Native alone"}          | d013
           65 | Population | {D014,Total:,"Hispanic or Latino:","Asian alone"}                                      | d014
           66 | Population | {D015,Total:,"Hispanic or Latino:","Native Hawaiian and Other Pacific Islander alone"} | d015
           67 | Population | {D016,Total:,"Hispanic or Latino:","Some Other Race alone"}                            | d016
           68 | Population | {D017,Total:,"Hispanic or Latino:","Two or More Races"}                                | d017
(7 行记录)

UPDATE census.lu_fact_types AS f
SET short_name = replace(replace(lower(f.fact_subcats[4]),' ','_'),':','')
WHERE f.fact_subcats[3]='Hispanic or Latino:' AND f.fact_subcats[4] > ''
RETURNING fact_type_id, short_name;

ostgres@localhost:5436 postgresql_book# UPDATE census.lu_fact_types AS f
postgresql_book-# SET short_name = replace(replace(lower(f.fact_subcats[4]),' ','_'),':','')
postgresql_book-# WHERE f.fact_subcats[3]='Hispanic or Latino:' AND f.fact_subcats[4] > ''
postgresql_book-# RETURNING fact_type_id, short_name;
 fact_type_id |                    short_name
--------------+--------------------------------------------------
           62 | white_alone
           63 | black_or_african_american_alone
           64 | american_indian_and_alaska_native_alone
           65 | asian_alone
           66 | native_hawaiian_and_other_pacific_islander_alone
           67 | some_other_race_alone
           68 | two_or_more_races
(7 行记录)

在查询中使用复合数据类型

PostgreSQL 会在建表时自动创建一个结构与表完全相同的数据类型, 其中包括了多个其他数据类型的成员字段, 因此也会被称为复合数据类型

SELECT x FROM census.lu_fact_types As x LIMIT 2;

postgres@localhost:5436 postgresql_book# SELECT x FROM census.lu_fact_types As x LIMIT 2;
                                                      x
-------------------------------------------------------------------------------------------------------------
 (1,Housing,"{S01,""OCCUPANCY STATUS"",""Total housing units"",HD01,Number}",s01)
 (2,Housing,"{S02,""OCCUPANCY STATUS"",""Total housing units"",""Occupied housing units"",HD01,Number}",s02)
(2 行记录)

DO

DO 命令可以执行一个基于过程化语言的匿名代码段.

在执行下面的程序前请将 census.lu_fact_types 表进行备份. 当然, 如果有最初的原始数据, 可以重新恢复. 下面使用 pg_dump.exe 备份数据库postgresql_book中的表 census.lu_fact_types 到文件 D:\work\cs\Database\postgres\book\postgresql_book_2e\census.lu_fact_types.sql

PS D:\work\cs\Database\postgres\book\postgresql_book_2e> C:\lnotes\pgsql\bin\pg_dump.exe -Upostgres -d postgresql_book -p5436 -t census.lu_fact_types > census.lu_fact_types.sql

当然, 如果不想破坏原来表中的数据, 可以将下面所建的表换个名称, 比如 lu_fact_types2. 建议将下面的代码另存为一个文件, 比如保存到文件 D:\work\cs\Database\postgres\book\eg_7_18.sql . 使用 vscode 编辑会有代码高亮.

set search_path=census;
DROP TABLE IF EXISTS lu_fact_types;
CREATE TABLE lu_fact_types(
  fact_type_id serial,
  category varchar(100),
  fact_subcats varchar(255)[],
  short_name varchar(50),
  CONSTRAINT pk_lu_fact_types PRIMARY KEY(fact_type_id)
);

DO language plpgsql
$$
DECLARE var_sql text;
BEGIN
	var_sql := string_agg(
		'INSERT INTO lu_fact_types(category, fact_subcats, short_name)
		SELECT
			''Housing'',
			array_agg(s' || lpad(i::text,2,'0') || ') As fact_subcats,
			' || quote_literal('s' || lpad(i::text,2,'0')) || ' As short_name
		FROM staging.factfinder_import
		WHERE s' || lpad(I::text,2,'0') || ' ~ ''^[a-zA-Z]+'' ', ';'
	)
	FROM generate_series(1,51) As I;
	EXECUTE var_sql;
END
$$;

postgres@localhost:5436 postgresql_book# set search_path=census;
SET
时间：0.813 ms

postgres@localhost:5436 postgresql_book# select count(*) from lu_fact_types;
 count
-------
    68
(1 行记录)

在删除前查询一下表中的数据, 上面显示有68条记录. 要小心, 做好备份.

postgres@localhost:5436 postgresql_book# DROP TABLE IF EXISTS lu_fact_types;
ERROR:  cannot drop table lu_fact_types because other objects depend on it
描述:  view vw_facts depends on table lu_fact_types
提示:  Use DROP ... CASCADE to drop the dependent objects too.
时间：81.085 ms

显示有其他对象(视图vw_facts)依赖此表, 无法删除. 可以使用 DROP ... CASCADE 去删除这些依赖的对象.

适用于聚合操作的 FILTER 子句

适用于聚合操作的 FILTER 子句

9.4 版中引入了用于聚合操作的 FILTER 子句. 这是 ANSI SQL 标准中的一个关键字. 该关键字用于替代同为 ANSI SQL 标准语法的 CASE WHEN 子句, 使聚合操作的语法得以简化.

示例

使用 CASE WHEN 子句来统计每个学生不同科目的多次测试的平均成绩.

SELECT student,
	AVG(CASE WHEN subject='algebra' THEN score ELSE NULL END) As algebra,
	AVG(CASE WHEN subject='physics' THEN score ELSE NULL END) As physics
FROM test_scores
GROUP BY student;

使用 FILTER 子句可以实现与上面语句等价的效果, 语法如下.

SELECT student,
	AVG(score) FILTER (WHEN subject='algebra') As algebra,
	AVG(score) FILTER (WHEN subject='physics') As physics
FROM test_scores
GROUP BY student;

对于求平均值、求合计值以及其他很多聚合函数来说, CASE 和 FILTER 子句是等价的, 即二者可以起到相同的作用.

FILTER 子句的优势在于写法比较清晰简洁并且操作大数据量时速度比较快.

CASE 语句对于筛选掉的字段值是当成 NULL 值处理的, 因此对于 array_agg 这种会处理NULL 值的聚合函数来说, 使用 CASE WHEN 子句就不止是写法繁琐的问题了, 还会导致输出不想要的结果.

FILTER 子句适用于所有聚合函数, 不仅仅是 PostgreSQL 中内置的那些聚合函数, 通过安装扩展包支持的聚合函数也是可以用的.

窗口函数

窗口函数

PostgreSQL 从8.4版开始支持 ANSI SQL 标准中规定的窗口函数特性.

通过使用窗口函数, 可以在当前记录行中访问到与其存在特定关系的其他记录行, 相当于在每行记录上都开了一个访问外部数据的窗口, 这也是“窗口函数”这个名称的由来.

“窗口”就是当前行可见的外部记录行的范围.

通过窗口函数可以把当前行的“窗口”区域内的记录的聚合运算结果附加到当前记录行.

row_number 和 rank 这类窗口函数能够基于窗口区的数据实现对记录行的复杂排序.

如果不借助窗口函数而又想要达到相同的效果, 就只能使用关联操作和子查询来实现.

表面上看, 使用窗口函数违背了 SQL 语言“基于结果集”的编程思想, 因为它为每一行数据拓展出了一个外部数据域. 但从另外一个角度看, 我们可以认为窗口函数本质上仅是一种用来替代关联操作和子查询的简写语法, 也就是说窗口函数并未突破 SQL 体系原有的运算逻辑, 那么这样就不算是违反了“基于结果集”的思想.

示例

下面的例子通过使用窗口函数, 可以在单个 SELECT 语句中同时获取到符合 fact_type_id=86 条件的记录的均值计算结果以及原始记录的详细信息.

SELECT tract_id, val, AVG(val) OVER() As val_avg
FROM census.facts
WHERE fact_type_id=86;

PARTITION BY 子句

ORDER BY 子句

CTE 表达式

CTE 表达式

公用表表达式(CTE)本质上来说就是在一个非常庞大的 SQL 语句中允许用户通过一个子查询语句先定义出一个临时表, 然后在这个庞大的 SQL 语句的不同地方都可以直接使用这个临时表.

基本 CTE 用法介绍

可写 CTE 用法介绍

递归 CTE 用法介绍

LATERAL 横向关联语法

LATERAL 横向关联语法

End

Thanks very much!