第5章 数据类型_bigserial

第 5 章 数据类型

PostgreSQL 也支持数字型、字符串型、日期型、时间型以及布尔型等业界常用的数据类型。但 PostgreSQL 的先进之处在于它还支持数组、带时区的日期时间、时间间隔、区间、JSON、XML 以及其他很多数据类型，此外还支持用户自定义数据类型。

当使用运算符时，请记住它在面对不同的数据类型时所代表的含义是不同的。比如加号对数字来说就是相加，对区间类型来说就是区间的并集。

5.1 数值类型

PostgreSQL 支持常用的整数、小数、浮点数等数字类型。

5.1.1 serial类型

serial 类型和它的兄弟类型 bigserial 是两种可以自动生成递增整数值的数据类型，一般如果表本身的字段不适合作为主键字段，会增加一个专门的字段并指定为 serial 类型以作为主键。

创建表时如果指定了一个字段类型为 serial，那么 PostgreSQL 会首先将其作为整型处理，同时自动在该表所在 schema 中创建一个名为 table_name_column_name_seq 的序列。然后设定该序列为该整型字段的取值来源。如果修改了表定义并删除此 serial 字段，那么系统同时也会自动删除附属的序列。

使用 ALTER SEQUENCE 语句来管理该类对象。可以设置其当前值和边界值（也就是最大值和最小值），还可以设置每次递增的步长。虽然一般来说序列值都是递增的，但你也可以将其设为递减，只要将步长值 increment 设为负数即可。如果需要生成一个跨越多表的唯一键值，那么这种多表共享序列的用法特别方便。如需多表共享同一个现存的序列号生成器，先为每张表新增一个类型为 integer 或者 bigint 的字段，然后指定其默认值为 nextval(sequence_name) 即可。

CREATE SEQUENCE s START 1;
CREATE TABLE stuff(id bigint DEFAULT nextval('s') PRIMARY KEY, name text);
1
2

如果你重命名了一张含 serial 字段的表，这张表的 serial 字段关联的序列号生成器是不会跟着改名的，但关联运作机制是不受影响的。为避免混淆，可以手动修改序列号生成器的名称以保持与表名一致。

5.1.2 生成数组序列的函数

PostgreSQL 有一个名为 generate_series 的灵活又实用的数组生成函数，它可以用来生成一个按一定步长递增的整数序列，也可以用来生成一个以一定时间间隔作为步长来递增的日期或者时间戳序列。generate_series 函数的方便之处在于，你可以使用它有效地模仿 SQL 中的 for 循环。

使用 generate_series() 函数生成步长为 13 的整数序列。另外请注意：结束值将永远不会超出我们指定的区间，因此，尽管我们的区间结束于 51，但最后一个数字是 40，因为 40 再加上 13 就会超出上限。

SELECT x FROM generate_series(1,51,13) As x;
x
----
1
14
27
40
1
2
3
4
5
6
7

5.2 文本类型

PostgreSQL 有三种最基础的文本数据类型：character（也称为 char）、character varying（也称为 varchar）和 text。

如果存储的字符长度达不到 char 类型的定义长度，那么 PostgreSQL 会自动在后面用空格填充，直到填满定义的长度为止。右补齐空格机制会导致存储空间的浪费，得到的好处是可以确保字符串是定长的。可以确认的是，char 比起 varchar 和 text 没有任何性能优势，却一定会占用更多的空间。如果要存储变长字符串，请使用 varchar 类型。text 类型是最通用的字符存储类型，不需要设最大长度。varchar 和 text 类型字段的存储空间上限均为约 1GB，这是一个很大的值了。事实上，系统在后台会用 TOAST 机制处理超过一个物理存储页大小的内容。

5.2.1 字符串函数

常见的字符串操作包括：填充（lpad、rpad）、修整空白（rtrim、ltrim、trim、btrim）、提取子字符串（substring）以及连接（||）。

使用 lpad 和 rpad 进行填充操作

SELECT
lpad('ab', 4, '0') As ab_lpad,
rpad('ab', 4, '0') As ab_rpad,
lpad('abcde', 4, '0') As ab_lpad_trunc; ➊

ab_lpad 	| ab_rpad 	| ab_lpad_trunc
------------+-----------+---------------
00ab 		| ab00 		| abcd
1
2
3
4
5
6
7
8

➊ 如果字符串超过指定长度，lpad 不但不会填充，反而会对其进行截断。

默认情况下，trim 函数用于移除空格，但你也可以传入一个可选参数，指示要剪裁的其他字符（char数据类型的定长字段是不能去掉空白的，需要特别注意的）。

SELECT
a As a_before, trim(a) As a_trim, rtrim(a) As a_rt,
i As i_before, ltrim(i, '0') As i_lt_0,
rtrim(i, '0') As i_rt_0, trim(i, '0') As i_t_0
FROM (
SELECT repeat(' ', 4) || i || repeat(' ', 4) As a, '0' || i As i
FROM generate_series(0, 200, 50) As i
) As x;

a_before	 | a_trim	| a_rt	| i_before	| i_lt_0	| i_rt_0	| i_t_0
-------------+----------+-------+-----------+-----------+-----------+-------
0 			| 0 		| 0 	| 00 		| 			| 			|
50 			| 50 		| 50 	| 050 		| 50 		| 05 		| 5
100 		| 100 		| 100 	| 0100 		| 100 		| 01 		| 1
150 		| 150 		| 150 	| 0150 		| 150 		| 015 		| 15
200 		| 200 		| 200 	| 0200 		| 200 		| 02 		| 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

5.2.2 将字符串拆分为数组、表或者子字符串

split_part 函数可以将指定位置的元素从用固定分隔符分隔的字符串中取出来。取出分隔符字符串中的第 2 个元素。

SELECT split_part('abc.123.z45','.', 2) As x;
x
---
123
1
2
3
4

string_to_array 函数可以将基于固定分隔符的字符串拆分为一个数组。通过结合使用 string_to_array 和 unnest 函数，可以将一个字符串展开为若干记录行。

SELECT unnest(string_to_array('abc.123.z45', '.')) As x;
x
---
abc
123
z45
1
2
3
4
5
6

5.2.3 正则表达式和模式匹配

PostgreSQL 对正则表达式的支持是极其强大的。你可以设定查询返回结果的格式为表或者数组，并且对其进行极其复杂的替换和更新操作。包括逆向引用（back reference）在内的一些高级搜索方法都是支持的。

使用逆向引用技术对电话号码进行重新格式化

SELECT regexp_replace(
'6197306254',
'([0-9]{3})([0-9]{3})([0-9]{4})',
E'\(\\1\) \\2-\\3'
) As x;
x
--------------
(619) 730-6254
1
2
3
4
5
6
7
8

将文本中的电话号码作为单独的行返回

SELECT unnest(regexp_matches(
'Cell (619) 852-5083. Work (619)123-4567 , Casa 619-730-6254. Bésame mucho.',
E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}', 'g')
) As x;

x
--------------
(619) 852-5083
(619)123-4567
619-730-6254
(3 rows)
1
2
3
4
5
6
7
8
9
10
11

regexp_matches 函数会返回根据一个正则表达式筛选匹配得到的字符串数组。该函数的最后一个入参名为 flags，我们为其输入的值是 g，g 代表 global，即需要进行完整搜索并返回所有匹配上的字符串，每个字符串作为数组中的一个元素。如果不填该 flags 参数，那么返回的结果只会包含第一个命中的字符串。在做正则匹配时不区分这些字符的大小写，那么可以用 gi 这个复合标记。

如果只需要命中第一条，那么可以使用 substring 函数。

SELECT substring(
'Cell (619) 852-5083. Work (619)123-4567 , Casa 619-730-6254. Besame mucho.'
from E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}')
As x;
x
----------------
(619) 852-5083
(1 row)
1
2
3
4
5
6
7
8

还可以将正则表达式与 SIMILAR TO（~）运算符一起使用。

SELECT description
FROM mytable
WHERE description ~
E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}';
1
2
3
4

5.3 时间类型

PostgreSQL 对时间类型的支持在业界是无人能及的。除了常见的日期和时间类型，PostgreSQL 还支持时区，并能够按照不同的时区对夏令时进行自动转换。此外 PostgreSQL 还支持一些特殊的数据类型，如interval，该类型可以用于对日期时间进行数学运算。PostgreSQL 还有正无穷大和负无穷大的概念，这样我们就不用为了表达这两个概念而弄出一些奇奇怪怪的潜规则，搞这些潜规则迟早会导致问题。

对于其中支持时区的类型，如果数据库服务器所在的时区发生了改变，则该类型中存储的数据会自动针对新的时区进行调整以保证时间一致。

date
　　该类型仅存储月、日、年，没有时区、小时、分和秒的信息。

time（又称 time without time zone）
　　该类型仅存储小时、分、秒信息，不带日期和时区信息。

timestamp（又称 timestamp without time zone）
　　该类型存储了日期（年、月、日）和时间（时、分、秒）数据，但不带时区信息。因此，即使你修改了数据库服务器所在的时区信息，该类字段查询出来显示的值也是固定不变的。

timestamptz（又称 timestamp with time zone）
　　该类型同时存储了日期、时间以及时区信息。在系统内部，该类型的字段值是以 UTC 世界标准时间格式存储的，但当查询显示时，会按照服务器的时区设置进行换算后再显示（时区也可以在库级 / 用户级 / 会话级分别进行设置）。如果你输入的时间戳不带时区数据，那么存入 timestamptz 类型字段中时，PostgreSQL 会自动使用当前数据库服务器的时区信息来补充。如果修改了数据库服务器的时区设置，你可以看到查询出来的时间数据发生了变化。

timetz（又称 time with time zone）
　　与 timestamptz 类型类似，但该类型的使用频率较低，因为它虽然携带了时区信息却没有日期信息。

interval
　　该类型描述了一个时间段的长度，单位可以是小时、天、月、分钟或者其他粒度。该类型适用于对日期和时间进行数学运算的场景。

tsrange
该类型可用于定义 timestamp with no timezone 的开区间和闭区间。该类型包含两个时间戳以及开区间和闭区间限定符。例如，’[2012-01-01 14:00 2012-01-01 15:00)’::tsrange 定义了从 14:00 开始到 15:00 之前结束的一个时间段。

5.3.1 时区详解

PostgreSQL 并没有存储时区信息，而只是使用时区信息来把日期和时间转换为 UTC 准时间再存储下来。此后就不需要时区信息了。当 PostgreSQL 需要显示该日期时间信息时，它会按顺序查找当前会话级、用户级、数据库级、服务器级的时区设置，然后使用找到的第一个时区来将 UTC 标准时间转换为对应时区的时间值后再显示。

这里有一个非常有趣的例子：一位出差中的销售员需要坐飞机回家，起点是旧金山，终点是奥克兰附近。当她登上飞机时，当地时钟显示的时间是 2012 年 3 月 11 日凌晨 1 点 50 分。当她降落时，当地时钟显示的时间是 2012 年 3 月 11 日凌晨 3 点 10 分。那么请问这段旅程共花了多长时间？要回答这个问题有一个关键点，那就是在这段飞行的过程中发生了夏令时的转换，也就是说时间向前跃迁了。如果使用了带时区信息的时间戳，算出来的时间间隔就是 20 分钟，对于一段仅仅跨越旧金山海湾的短途飞行来说，这个答案显然是可信的。如果我们不使用带时区信息的数据类型，一定会得到错误的答案。

SELECT '2012-03-11 3:10 AM America/Los_Angeles'::timestamptz
- '2012-03-11 1:50 AM America/Los_Angeles'::timestamptz;
1
2

以上查询得到的答案是 20 分钟，然而以下查询得到的答案却是 1 小时 20 分钟。

SELECT '2012-03-11 3:10 AM'::timestamp- '2012-03-11 1:50 AM'::timestamp;
1

输入时使用的是一个时区的本地时间，输出却是另一个时区的本地时间。请注意，输出显示附带了时差，这是没问题的，与我原始录入的时间之间仅仅是显示差异而已，在数据库系统内部是以 UTC 标准时间存储的。

SELECT '2012-02-28 10:00 PM America/Los_Angeles'::timestamptz;
2012-02-29 01:00:00-05
1
2

将带时区信息的时间戳数据转换为不带时区的时间戳数据

SELECT '2012-02-28 10:00 PM America/Los_Angeles'::timestamptz
AT TIME ZONE 'Europe/Paris';
2012-02-29 07:00:00
1
2
3

5.3.2 日期时间类型的运算符和函数

通过 interval 类型，可以使用我们很熟悉的加减运算符对日期和时间进行相加或者相减操作。

相加运算符（+）可以在一个时间类型值上加上一段时间间隔：

SELECT '2012-02-10 11:00 PM'::timestamp + interval '1 hour';
2012-02-11 00:00:00
1
2

将两个 interval 类型直接相加：

SELECT '23 hours 20 minutes'::interval + '1 hour'::interval;
24:20:00
1
2

相减运算符（-）可以从一个时间类型值中减去一段时间间隔：

SELECT '2012-02-10 11:00 PM'::timestamptz - interval '1 hour';
2012-02-10 22:00:00-05
1
2

区间重叠 overlaps 函数，前两个是第一个时间段的首尾时间点，后两个是第二个时间段的首尾时间点。OVERLAPS 运算符会将这两个时间段看作半开半闭区间，也就是说起始时点包含在时段内，结束时点不包含在时段内。这与 BETWEEN 谓词运算符的逻辑是不一样的，BETWEEN 会认为起始点和结束点都是包含在区间内的。

对时间戳和日期类型使用 OVERLAPS 运算符

SELECT
('2012-10-25 10:00 AM'::timestamp, '2012-10-25 2:00 PM'::timestamp)
OVERLAPS
('2012-10-25 11:00 AM'::timestamp,'2012-10-26 2:00 PM'::timestamp) AS x,
('2012-10-25'::date,'2012-10-26'::date)
OVERLAPS
('2012-10-26'::date,'2012-10-27'::date) As y;

x 	|y
----+---
t 	|f
1
2
3
4
5
6
7
8
9
10
11

使用 generate_series() 函数来生成时间序列数组

SELECT (dt - interval '1 day')::date As eom
FROM generate_series('2/1/2012', '6/30/2012', interval '1 month') As dt;
eom
------------
2012-01-31
2012-02-29
2012-03-31
2012-04-30
2012-05-31
1
2
3
4
5
6
7
8
9

从日期时间类型中提取部分元素

SELECT dt, date_part('hour',dt) As hr, to_char(dt,'HH12:MI AM') As mn
FROM
generate_series(
'2012-03-11 12:30 AM',
'2012-03-11 3:00 AM',
interval '15 minutes'
) As dt;

dt 						| hr 	| mn
------------------------+-------+----------
2012-03-11 00:30:00-05 	| 0 	| 12:30 AM
2012-03-11 00:45:00-05 	| 0 	| 12:45 AM
2012-03-11 01:00:00-05 	| 1 	| 01:00 AM
2012-03-11 01:15:00-05 	| 1 	| 01:15 AM
2012-03-11 01:30:00-05 	| 1 	| 01:30 AM
2012-03-11 01:45:00-05 	| 1 	| 01:45 AM
2012-03-11 03:00:00-04 	| 3 	| 03:00 AM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

generate_series 函数默认生成的是 timesatamptz 类型的数据，需要显式转换为 timestamp 类型。

5.4 数组类型

如果你自定义了一个数据类型，那么 PostgreSQL 会在后台自动为此类型创建一个数组类型。例如，integer 有一个相应的整数数组类型 integer[]，character 也有相应的字符数组类型character[]，以此类推。

5.4.1 数组构造函数

最基本的构造数组的方法就是一个个元素手动录入，语法如下：

SELECT ARRAY[2001, 2002, 2003] As yrs;
1

如果数组元素存在于一个查询返回的结果集中，那么可以使用这个略复杂一些的构造函数 array() 来生成数组：

SELECT array(
SELECT DISTINCT date_part('year', log_ts)
FROM logs
ORDER BY date_part('year', log_ts)
);
1
2
3
4
5

把一个直接以字符串格式书写的数组转换为一个真正的数组，语法如下：

SELECT '{Alex,Sonia}'::text[] As name, '{46,43}'::smallint[] As age;
name | age
-------------+--------
{Alex,Sonia} | {46,43}
1
2
3
4

将一个分隔符格式的字符串转换为数组

SELECT string_to_array('ca.ma.tx', '.') As estados;
estados
----------
{CA,MA,TX}
(1 row)
1
2
3
4
5

array_agg 是一种聚合函数，它可用于将一组任何类型的数据转换为数组。

SELECT array_agg(log_ts ORDER BY log_ts) As x
FROM logs
WHERE log_ts BETWEEN '2011-01-01'::timestamptz AND '2011-01-15'::timestamptz;
x
------------------------------------------
{'2011-01-01', '2011-01-13', '2011-01-14'}
1
2
3
4
5
6

根据一维数组构建多维数组

SELECT array_agg(f.t)
FROM ( VALUES ('{Alex,Sonia}'::text[]),
('{46,43}'::text[] ) ) As f(t);
array_agg
----------------------
{{Alex,Sonia},{46,43}}
(1 row)
1
2
3
4
5
6
7

不过要想使用该功能，被聚合的基础数组中的元素类型必须相同，而且被聚合的基础数组的维度必须一样。

5.4.2 将数组元素展开为记录行

使用 unnest 函数将数组纵向展开

SELECT unnest('{XOX,OXO,XOX}'::char(3)[]) As tic_tac_toe;
tic_tac_toe
---
XOX
OXO
XOX
1
2
3
4
5
6

你可以在一个 SELECT 语句中使用多个 unnest 函数，但如果每个 unnest 展开后的记录行数不一致，或者说“对不齐”，那么得到的最终结果将是这些结果集之间的笛卡儿积。

多个可对齐数组的展开效果

SELECT
unnest('{three,blind,mice}'::text[]) As t,
unnest('{1,2,3}'::smallint[]) As i;
t 		|i
--------+-
three 	|1
blind 	|2
mice 	|3
1
2
3
4
5
6
7
8

多个无法对齐的数组展开后的效果
SELECT
unnest( '{blind,mouse}'::varchar[]) As v,
unnest('{1,2,3}'::smallint[]) As i;
v 		|i
--------+-
blind 	|1
mouse 	|2
blind 	|3
mouse 	|1
blind 	|2
mouse 	|3
1
2
3
4
5
6
7
8
9
10
11
12

使用支持多入参的 unnest 展开不平衡数组（如果某个数组相对其他数组来说元素个数少，则缺少的那些元素的位置就是不平衡的位置。不平衡的位置会填入空值 null 。）

SELECT * FROM unnest('{blind,mouse}'::text[], '{1,2,3}'::int[]) AS f(t,i);
t 		| i
--------+---
blind 	| 1
mouse 	| 2
<NULL> 	| 3
1
2
3
4
5
6

5.4.3 数组的拆分与连接

PostgreSQL 支持使用 start:end 语法对数组进行拆分。操作结果是原数组的一个子数组。例如，如果要得到一个仅包含当前数组第 2 个至第 4 个元素的新数组，可以使用以下语法：

SELECT fact_subcats[2:4] FROM census.lu_fact_types;
1

如果要将两个数组连接到一起，可以使用连接运算符||：

SELECT fact_subcats[1:2] || fact_subcats[3:4] FROM census.lu_fact_types;
1

可以通过下面的语法来为一个现有数组添加元素：

SELECT '{1,2,3}'::integer[] || 4 || 5;
1

得到的结果是 {1,2,3,4,5}。

5.4.4 引用数组中的元素

一般来说，我们会通过数组下标来引用数组元素，请特别注意 PostgreSQL 的数组下标从 1 开始。如果你试图越界访问一个数组，也就是说数组下标已经超过了数组元素的个数，那么不会返回错误，而是会得到一个空值 NULL。下面的例子演示了获取数组的第一个和最后一个元素的方法：

SELECT
fact_subcats[1] AS primero,
fact_subcats[array_upper(fact_subcats, 1)] As segundo
FROM census.lu_fact_types;
1
2
3
4

我们使用 array_upper 函数来获取数组元素的个数，该函数的第二个必选入参表示数组的维度。在本例中，数组是一维的，但 PostgreSQL 支持多维数组。

5.4.5 数组包含性检查

数组类型的运算符还有 =、<>、<、>、@>、<@ 以及 &&。这些运算符要求两边数组的数据类型相同。如果你在数组类型的字段上建立了 GiST或者 GIN 索引，那么这些运算符可以用上索引。

重叠判定运算符（&&）的作用是：如果两个数组有任何共同的元素，则返回 true；否则返回 false。

SELECT fact_subcats
FROM census.lu_fact_types
WHERE fact_subcats && '{OCCUPANCY STATUS,For rent}'::varchar[];

fact_subcats
-----------------------------------------------------------
{S01,"OCCUPANCY STATUS","Total housing units"...}
{S02,"OCCUPANCY STATUS","Total housing units"...}
{S03,"OCCUPANCY STATUS","Total housing units"...}
{S10,"VACANCY STATUS","Vacant housing units","For rent"...}
(4 rows)
1
2
3
4
5
6
7
8
9
10
11

只有当两个数组中的所有元素及其排列顺序都完全相同时，等值判定运算符（=）才会返回 true。如果你并不关心两个数组的元素顺序是否完全相同，只是想知道一个数组中的元素集合是否是另一个数组的子集，可以使用包含关系判定运算符（包含@>、被包含<@）。

SELECT '{1,2,3}'::int[] @> '{3,2}'::int[] AS contains;
contains
--------
t
(1 row)

SELECT '{1,2,3}'::int[] <@ '{3,2}'::int[] AS contained_by;
contained_by
------------
f
(1 row)
1
2
3
4
5
6
7
8
9
10
11

5.5 区间类型

PostgreSQL 为区间类型提供了很多配套的运算符和函数，例如判定区间是否重叠，判定某个值是否落在区间内，以及将相邻的若干区间合并为一个完整的区间等。

• 整数区间 (-2,2] 含四个元素：-1、0、1、2。
• 整数区间 (-2,2) 含三个元素：-1、0、1。
• 整数区间 [-2,2] 含五个元素：-2、-1、0、1、2。

5.5.1 离散区间和连续区间

PostgreSQL 对离散区间和连续区间是区别对待的。整数类型或者日期类型的区间是离散区间，因为区间内每一个值都是可以被枚举出来的。数字区间或者时间戳区间就是一个连续区间，因为区间内的值有无限多。

PostgreSQL 会自动对所有的离散区间进行规范化，不管是存储还是显示时都会这么做。因此，如果你输入了一个时间区间 (2014-1-5,2014-2-1]，那么 PostgreSQL 会自动把它改写为 [2014-01-06,2014-02-02)。

5.5.2 原生支持的区间类型

int4range、int8range
　　这是整数型离散区间，其定义符合前闭后开的规范化要求。
numrange
　　这是连续区间，可以用于描述小数、浮点数或者双精度数字的区间。
daterange
　　这是不带时区信息的日期离散区间。
tsrange、tstzrange
　　这是时间戳（日期加时间）类型的连续区间，秒值部分支持小数。tsrange 不带时区信息，tstzrange 带时区信息。对于数字类型的区间来说，如果区间的起点值或者终点值未指定，那么 PostgreSQL 会自动为其填入 null 值。理论上讲，你可以将该 null 解释为代表左侧的 -infinity（负无穷）或右侧的infinity（正无穷）。实际上，你会受限于特定数据类型的最小值和最大值。比如对于 int4range 数据类型来说，区间 (,) 实际上代表的是 [-2147483648,2147483647)。

对于时间类型的区间来说，-infinity 和 infinity 就是有效的上限和下限。

5.5.3 定义区间的方法

任何类型的区间都是由相同数据类型的起点值和终点值外加表示区间开闭的符号 [ 、] 、( 、) 构成的。

使用类型转换的方法来定义区间

SELECT '[2013-01-05,2013-08-13]'::daterange; ➊
SELECT '(2013-01-05,2013-08-13]'::daterange; ➋
SELECT '(0,)'::int8range; ➌
SELECT '(2013-01-05 10:00,2013-08-13 14:00]'::tsrange; ➍
[2013-01-05,2013-08-14)
[2013-01-06,2013-08-14)
[1,)
("2013-01-05 10:00:00","2013-08-13 14:00:00"]
1
2
3
4
5
6
7
8

❶ 定义了一个从 2013-01-05 到 2013-08-13 的日期型闭区间。
❷ 定义了一个从 2013-01-05 到 2013-08-13 的日期型半开半闭区间。
❸ 定义了一个大于 0 的整数区间。
❹ 定义了一个从 2013-01-05 10:00 AM 到 2013-08-13 2 PM 的半开半闭连续区间。

PostgreSQL 中的日期时间类型可以用 -infinity 表示负无穷，用 infinity 表示正无
穷。为了与传统写法保持一致，建议以约定俗成的“前闭后开”方式来书写时间范围，即区间起点使用左中括号“[”，区间终点使用右小括号“)”，比如 [-infinity, intifinity)。区间也可以使用区间构造函数来定义，该构造函数的名称与区间类型名称是一致的，可以输入两个或者三个参数。示例如下：

SELECT daterange('2013-01-05','infinity','[]');
1

第三个参数是区间边界开闭标识符，如果不填则默认为前开后闭 [)。

5.5.4 定义含区间类型字段的表

建立一个带有日期区间类型字段的表

CREATE TABLE employment (id serial PRIMARY KEY, employee varchar(20),
period daterange);
CREATE INDEX ix_employment_period ON employment USING gist (period);➊
INSERT INTO employment (employee,period)
VALUES
('Alex','[2012-04-24, infinity)'::daterange),
('Sonia','[2011-04-24, 2012-06-01)'::daterange),
('Leo','[2012-06-20, 2013-04-20)'::daterange),
('Regina','[2012-06-20, 2013-04-20)'::daterange);
1
2
3
4
5
6
7
8
9

➊ 在区间字段上建立一个 GiST 索引。

5.5.5 适用于区间类型的运算符

01. 重叠运算符
顾名思义，重叠判定运算符 && 的作用就是判定两个区间是否有重叠部分，如果有则返回 true，否则返回 false。
查询谁与谁曾经同时在公司工作过

SELECT
e1.employee,
string_agg(DISTINCT e2.employee, ', ' ORDER BY e2.employee) As colleagues
FROM employment As e1 INNER JOIN employment As e2
ON e1.period && e2.period
WHERE e1.employee <> e2.employee
GROUP BY e1.employee;

employee 	| colleagues
------------+-------------------
Alex 		| Leo, Regina, Sonia
Leo 		| Alex, Regina
Regina 		| Alex, Leo
Sonia 		| Alex
1
2
3
4
5
6
7
8
9
10
11
12
13
14

02. 包含与被包含关系运算符
对于包含关系运算符 @> 来说，第一个参数是区间，第二个参数是待判定的值。如果第二个参数的值落在第一个参数的区间内，运算符就返回 true，否则返回 false。

查询当前还在公司工作的雇员名单

SELECT employee FROM employment WHERE period @> CURRENT_DATE GROUP BY employee;
employee
----
Alex
1
2
3
4

<@ 是用于判定被包含关系是否成立的运算符，它的第一个参数是待判定的值，第二个参数是区间，其用法与包含关系运算符完全一致。

5.6 JSON数据类型

PostgreSQL 支持 JSON 数据类型并提供了很多相关的函数。JSON 已成为 Web 开发领域最流行的数据传递格式。PostgreSQL 9.3 中针对 JSON 类型做了显著的功能增强，新增了一些用于实现子树抽取、内容编辑以及与其他数据类型进行互转的操作函数。PostgreSQL 9.4 中引入了 JSONB 数据类型，该类型是 JSON 类型的二进制版本，它与 JSON 类型最主要的差别是 JSONB 可以支持索引而 JSON 不能（只能使用非常受限制的函数索引）。PostgreSQL 9.5 中为 JSONB 类型引入了更多的支持函数，包括可用于修改 jsonb 对象中的子元素的函数。PostgreSQL 9.6 中引入了 josnb_insert 函数，可以实现往一个现有的 jsonb 对象中插入数据，即支持往 JSONB 内部的数组对象中增加新元素，也可以实现在 JSONB 内部增加一个新的键值对。

5.6.1 插入JSON数据

要想在表中存储 JSON 数据，只需建一个 json 类型的字段即可，语法如下：

CREATE TABLE persons (id serial PRIMARY KEY, person json);
1

PostgreSQL 会自动对插入的 JSON 文本进行格式检查以确保其格式合法。请注意，无法将无效的 JSON 字符串存储到某个 JSON 列中，而且也没有什么办法可以把无效的 JSON 字符串转换为 JSON 类型。

插入一个 JSON 字段

INSERT INTO persons (person)
VALUES (
'{
"name":"Sonia",
"spouse":
{
"name":"Alex",
"parents":
{
"father":"Rafael",
"mother":"Ofelia"
},
"phones":
[
{
"type":"work",
"number":"619-722-6719"
},
{
"type":"cell",
"number":"619-852-5083"
}
]
},
"children":
[
{
"name":"Brandon",
"gender":"M"
},
{
"name":"Azaleah",
"girl":true,
"phones": []
}
]
}'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

5.6.2 查询JSON数据

要想对 JSON 数据的层次化结构进行访问，最简单的方法就是使用路径指向符。

查询 JSON 字段

SELECT person->'name' FROM persons;
SELECT person->'spouse'->'parents'->'father' FROM persons;
1
2

也可以通过路径数组的形式进行查询，如下例所示：

SELECT person#>array['spouse','parents','father'] FROM persons;
1

注意，如果查询语句中使用了路径数组，前面必须用 #> 指向符。

可以通过指定下标来访问 JSON 内部数组的某个特定元素。请注意，JSON 数组的起始元素下标与 PostgreSQL 数组类型不同，前者从 0 开始，后者从 1 开始。

SELECT person->'children'->0->'name' FROM persons;
1

上面这句话用路径数组语法来表达就是：

SELECT person#>array['children','0','name'] FROM persons;
1

上面的这些例子中，查询返回的数据类型都是 JSON 内部元素的基础类型（数字、字符串、布尔值）。如果希望返回的值都统一转换为文本型，那么只需在路径指向符中再增加一个 > 符即可：

SELECT person->'spouse'->'parents'->>'father' FROM persons;
SELECT person#>>array['children','0','name'] FROM persons;
1
2

可以看到，如果联用多个 -> 指向符，只需把最后一个指向符改写为 ->> 即可。

json_array_elements 函数的入参是一个 JSON 数组，该函数会将该 JSON 数组中的每个元素拆成单独一行输出。用 json_array_elements 函数展开 JSON 数组。

SELECT json_array_elements(person->'children')->>'name' As name FROM persons;
name
-------
Brandon
Azaleah
(2 rows)
1
2
3
4
5
6

JSON 类型本质上表达了层次化结构的树型数据，当需要访问树上的元素时，强烈建议你使用指向符语法。PostgreSQL提供了一个功能与指向符语法对等的函数 json_extract_path，该函数可以接受不定长入参（也就是说该函数的入参个数可以无限多）。第一个参数是待访问的 JSON 对象，后续参数就是层次化访问路径上每一层的 key 值。->> 运算符也有一个功能完全对等的函数，名为json_extract_path_text，其用法类似。

5.6.3 输出JSON数据

PostgreSQL 除了可以查询库中已有的 JSON 数据外，还支持将别的数据类型转换为 JSON 类型。

将多条记录转换为单个 JSON 对象（PostgreSQL 9.3 及之后的版本才支持该语句）

SELECT row_to_json(f) As x
FROM (
SELECT id, json_array_elements(person->'children')->>'name' As cname FROM persons
) As f;
x
--------------------------
{"id":1,"cname":"Brandon"}
{"id":1,"cname":"Azaleah"}
(2 rows)
1
2
3
4
5
6
7
8
9

如果要将 persons 表中的所有记录行整体打包转换为一个 JSON 对象，可以使用以下语法：

SELECT row_to_json(f) As jsoned_row FROM persons As f;
1

“查询时将一行记录作为单个字段输出”这种功能只有 PostgreSQL 才支持。该功能对于创建复合 JSON 对象特别有用。

5.6.4 JSON类型的二进制版本：jsonb

该类型使用的运算符与 json 类型完全相同；该类型的处理函数与 json 类型的处理函数一一对应，仅在命名上略有差别（前者以“jsonb”开头，后者以“json”开头），另外 jsonb 类型还比 json 类型多了一些新的函数。由于 jsonb 类型的数据在存入库中时经过了预解析，因此在处理过程中无须再次进行文本解析，所以其处理性能远超 json 类型。

• json 是以原始文本格式存储的，而 jsonb 存储的是原始文本解析以后生成的二进制数据结构，该二进制结构中不再保存原始文本中的空格，存储下来的数字的形式也发生了一定的变化，并且对其内部记录属性值进行了排序。例如，文本中的 e-5 这种数字会被转换为对应的小数存储。
• jsonb 不允许其内部记录的键值重复，如果出现重复则会从中自动选择一条，其余的重复记录会被丢弃，但 json 类型中记录键值重复是允许的。
• jsonb 类型的字段上可以直接建立 GIN 索引，但 json 类型字段上却只能建立函数索引，因为只有通过函数才能从 JSON 的字符串中提取出具体字段值。

JSONB 与 JSON 类型输出格式对比

SELECT person As b FROM persons_b WHERE id = 1; ➊
SELECT person As j FROM persons WHERE id = 1; ➋
b
---------------------------------------------------------------------------------
{"name": "Sonia",
"spouse": {"name": "Alex", "phones": [{"type": "work", "number": "619-722-6719"},
{"type": "cell", "number": "619-852-5083"}],
"parents": {"father": "Rafael", "mother": "Ofelia"}},
"children": [{"name": "Brandon", "gender": "M"},
{"girl": true, "name": "Azaleah", "phones": []}]}
(1 row)
j
---------------------------------------------
{
"name":"Sonia",
"spouse":
{
"name":"Alex",
"parents":
{
"father":"Rafael",
"mother":"Ofelia"
},
"phones":
[
{
"type":"work",
"number":"619-722-6719"+
},
{
"type":"cell",
"number":"619-852-5083"+
}
]
},
"children":
[
{
"name":"Brandon",
"gender":"M"
},
{
"name":"Azaleah",
"girl":true,
"phones": []
}
]
}
(1 row)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

➊ 可以看出，jsonb 类型的输出是对输入的内容进行了重新格式化，并删掉了输入时文本中的空白。此外，插入记录时属性字段的顺序信息是不保留的。
➋ json 类型的输出保持了输入时的原样，包括原文本中的空白以及属性字段的顺序。

jsonb 比 json 多支持的运算符有以下几个：等值判定运算符（=）、包含关系判定运算符（@>）、被包含关系判定运算符（<@）、键值存在判定运算符（?）、判定一组键值中是否有任意一个已存在的运算符（?|），以及判定一组键值中的每一个是否均已存在的运算符（?&）。

JSONB 包含关系运算符的使用

SELECT person->>'name' As name
FROM persons_b
WHERE person @> '{"children":[{"name":"Brandon"}]}';

name
-----
Sonia
1
2
3
4
5
6
7

如果在 jsonb 列上创建了 GIN 索引，那么前述这几个运算符的操作速度是极快的：

CREATE INDEX ix_persons_jb_person_gin ON persons_b USING gin (person);
1

5.6.5 编辑JSONB类型的数据

PostgreSQL 9.5 中为了支持对 JSONB 类型的数据进行修改，引入了原生的 jsonb 连接运算符（||）以及删减运算符（-、#-），同时还引入了一些辅助操作函数。注意，json 类型并不支持这些运算符。

连接运算符可用于对一个 jsonb 对象新增或者替换其内部属性字段。使用 RETURNING 语法返回了更新后字段的值。

使用 JSONB 的||运算符来增加地址

UPDATE persons_b
SET person = person || '{"address": "Somewhere in San Diego, CA"}'::jsonb
WHERE person @> '{"name":"Sonia"}'
RETURNING person;

profile
-------------------------------------------------------------------------------
{"name": "Sonia", ... "address": "Somewhere in San Diego, CA", "children": ...}
(1 row)
UPDATE 1
1
2
3
4
5
6
7
8
9
10

由于 JSONB 类型要求内部属性字段的键值必须唯一，因此如果你试图插入一个重复名称的属性字段，原来的同名属性字段值会被替换掉。

使用 JSONB 的 - 运算符来移除某个属性

UPDATE persons_b
SET person = person - 'address'
WHERE person @> '{"name":"Sonia"}';
1
2
3

请注意，简单地使用 - 运算符只能删掉 JSONB 层次化树结构上第一层的元素。如果需要删除内部某一层的特定属性怎么办呢？此时可以使用 #- 运算符。该运算符使用一个表达了待删除属性所在路径的文本值数组作为入参。

使用 JSONB 的 #- 运算符删除内嵌的某个元素

UPDATE persons_b
SET person = person #- '{children,1,girl}'::text[]
WHERE person @> '{"name":"Sonia"}'
RETURNING person->'children'->1;

{"name": "Azaleah", "phones": []}
1
2
3
4
5
6

如需删除 JSONB 内部数组的某个特定元素，请指明其下标。由于 JavaScript 语言中数组元素的下标从 0 开始计数，所以如果要删除第二个子节点的某个元素，下标需写成 1 而不是 2。如果需删除整个名为“Azalean”的子节点，可以写成 ‘{children,1}’::text[]。

使用 jsonb_set 函数修改内部嵌套的属性值

UPDATE persons_b
SET person = jsonb_set(person,'{children,1,gender}'::text[],'"F"'::jsonb, true)
WHERE person @> '{"name":"Sonia"}';
1
2
3

jsonb_set 函数有四个入参，其定义的形式为：jsonb_set(jsonb_to_update,
text_array_path, new_jsonb_value, allow_creation)。如果将 allow_creation 置为 false，当所需修改的属性值不存在时，该函数会返回错误。

5.7 XML数据类型

XML 和 JSON 这两种数据类型都属于非规范化数据，在关系型数据库中存储这类数据其实是有争议的。然而，所有的高级关系型数据库（比如 IBM DB2、Oracle、SQL Server）中都支持 XML 数据类型。与 jsonb 数据类型不一样，目前没有哪种索引类型支持直接对 XML 数据类型进行索引，因此只能使用函数索引对其一部分数据进行索引，这一点与 json 是相同的。

5.7.1 插入XML数据

在往一个 xml 数据类型的列中插入数据时，PostgreSQL 会自动判定并确保只有格式合法的 XML 才会创建成功。text 类型字段中也可以存入一段 XML 文本，但是存入时不会进行格式合法性判断，这一点是 text 与 xml 类型的区别。

插入 XML 字段记录

CREATE TABLE families (id serial PRIMARYKEY, profile xml);
INSERT INTO families(profile)
VALUES(
'<family name="Gomez">
<member><relation>padre</relation><name>Alex</name></member>
<member><relation>madre</relation><name>Sonia</name></member>
<member><relation>hijo</relation><name>Brandon</name></member>
<member><relation>hija</relation><name>Azaleah</name></member>
</family>');
1
2
3
4
5
6
7
8
9

XML 数据的格式是千变万化的，你可以为 XML 字段设置一个 check 约束以确保输入的 XML 数据都符合某种格式。创建了一个 check 约束，该约束要求输入的 XML 数据中的 family 节点下都有一个 relation 节点。’/family/member/relation’ 是 XPath 语法，XPath 是一种能够在 XML 树状结构中定位到指定元素的语法。

确保所有 XML 字段记录中都有至少一个 member 节点和一个 relation 节点

ALTER TABLE families ADD CONSTRAINT chk_has_relation
CHECK (xpath_exists('/family/member/relation', profile));
1
2

如果试图插入这样一条记录：

INSERT INTO families (profile) VALUES ('<family name="HsuObe"></family>');
1

我们会看到这样的报错信息：ERROR: new row for relation “families” violates check constraint “chk_has_relation”（错误：试图插入“families”表中的新记录违反了约束“chk_has_relation”的要求）。

如果需要基于 DTD 或者 XSD 对 XML 数据进行格式检查，你需要自行编写格式检查函数，然后将此函数放到 check 约束中调用。PostgreSQL 目前还没有原生支持基于 DTD 或者 XSD 的格式检查。

5.7.2 查询XML数据

查询 XML 数据时，xpath 函数会发挥重要作用。该函数的第一个参数是一个 XPath 查询表达式，第二个参数是一个 xml 对象。查询结果是 XPath 查询语句所要查找的 XML 元素的列表。

查询 XML 字段

SELECT ordinality AS id, family,
(xpath('/member/relation/text()', f))[1]::text As relation,
(xpath('/member/name/text()', f))[1]::text As mem_name ➊
FROM (
SELECT
(xpath('/family/@name', profile))[1]::text As family, ➋
f.ordinality, f.f
FROM families, unnest(xpath('/family/member', profile)) WITH ORDINALITY AS f
) x;➌

id 	| family 	| relation 	| mem_name
----+-----------+-----------+----------
1 	| Gomez 	| padre 	| Alex
2 	| Gomez 	| madre 	| Sonia
3 	| Gomez 	| hijo 		| Brandon
4 	| Gomez 	| hija 		| Azaleah
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

❶ 获取每个 member 元素的 relation 标签和 name 标签中包含的文本元素。此处的语法中必须加数组下标，因为 xpath 语法返回的查询结果是数组类型的，即使返回的数组中只有一个元素也得加下标才能访问。
❷ 获取 family 根节点的 name 属性值。访问属性值的语法为 @attribute_name。
❸ 将 SELECT 语句的查询结果拆分为多个子元素，这些子元素包括 <member>、<relation>、</relation>、<name>、</name> 和 </member>。xpath 的斜杠语法表示要获取当前指定节点的子节点的内容。例如，xpath(’/family/member’, ‘profile’) 将以数组形式返回 profile 字段中 family 节点下所有 member 子节点的内容。xpath(’/family/@name’, ‘profile’) 返回的是 family 节点的 name 属性的值。默认情况下，xpath 返回的是包含前后标签部分的完整节点内容，加了 text() 以后，返回的就是该节点中包含的文本的内容。

PostgreSQL 10 中新增支持了 ANSI-SQL 中的 XMLTABLE 语法。XMLTABLE 可以基于预定义好的转换规则，将一段 XML 文本映射为独立的行和列。

使用 XMLTABLE 语法来查询 XML 数据

SELECT xt.*
FROM families,
XMLTABLE ('/family/member' PASSING profile ➊
COLUMNS ➋
id FOR ORDINALITY , ➌
family text PATH '../@name' , ➍
relation text NOT NULL , ➎
member_name text PATH 'name' NOT NULL
) AS xt;
id | family | relation | mem_name
----+--------+----------+----------
1 | Gomez | padre | Alex
2 | Gomez | madre | Sonia
3 | Gomez | hijo | Brandon
4 | Gomez | hija | Azaleah
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

❶ XMLTABLE 定义中的第一部分是一个 XML 路径，表明从 XML 对象的哪个具体位置抽取数据行。PASSING 关键字后跟字段名，表示从哪个字段中抽取行数据，该字段一定要是 xml 类型。此处我们使用 families 表的 profile 字段。
❷ COLUMNS 关键字表示下面即将定义从 XML 数据中抽取出来的字段列表。
❸ 此前介绍过，在支持返回结果集的函数中可以使用 WITH ORDINALITY 语法来标记所返回记录的行号，此处可以类似地使用 FOR ORDINALITY 语法来标记查询结果的行号。
❹ 你可以使用 …/ 表达式定位到当前行位置的上一层。此处我们使用 …/@name 来获取 family 节点的 name 属性，该属性所在的层级比 family/member 节点要高一级。@ 符号表示要取的是一个属性值（XML 中属性值的语法形式为 name=‘a value’）而不是一个元素。
❺ 如果原始 XML 中待取数据的路径上的元素名与预定义的转换规则中指定的字段名相同，则无须在规则定义中为目标字段设定 PATH 值（即该字段的数据来源路径）。本例中，由于原始 XML 中 /family/member/ralation 这个路径上的 relation 元素名与转换规则中定义的 relation 字段同名，所以无须在这里写 PATH 来指定数据来源路径。

5.8 全文检索

要想实现通过关键词对文本内容进行搜索，可以使用常见的 like 或者 ilike（忽略大小写的 like）这样的匹配方法，也可以使用强大的正则表达式或者 soundex 语音匹配算法。但这些方法都存在一个问题：它们无法实现基于自然语言的匹配。

FTS（full text search，全文检索）是一个带有一定“智能”的搜索工具包。虽然它离能够理解人类的真实想法还差得远，但是它能够在搜索过程中找到意义相近的词，而不仅仅是拼写相近的词。FTS 是 PostgreSQL 原生自带的一个功能模块，不需要单独安装。

FTS 的核心是一个被称为“FTS 配置库”的东西。这个库记录了词与词之间的语义匹配规则，其依据是一本或者多本词典。词典中还可以包含停止词，它是指一段话中意义不大的那部分内容。冠词、连接词、介词、代词都是停止词，比如 a、the、on、that 等。FTS 能够实现同义词搜索以及对无意义停止词的过滤，此外它还能够设置搜索结果的排名规则。FTS 可以根据词与词之间的邻近程度以及关键词出现的频度来为搜索结果排名。

5.8.1 FTS配置库

大多数 PostgreSQL 发行版中都自带了 10 个以上的 FTS 配置库，这些配置库都安装在 pg_catalogschema 中。可以通过 SELECT cfgname FROM pg_ts_config; 语句或者是 psql 的 \dF 命令来查出所有已安装的配置库。一般情况下查询结果如下：

cfgname
----------
simple
danish
dutch
english
finnish
french
german
hungarian
italian
norwegian
portuguese
romanian
russian
spanish
swedish
turkish
(16 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

PostgreSQL 并不要求用户使用系统自带的 FTS 配置库，而是允许用户创建自定义的 FTS 配置库。如果你要检索西班牙文的文本，也可以找到为你所要的西班牙方言量身定做的配置库。

从 hunspell_dicts 下载 hunspell 配置库。该库支持多种语言，我们选择安装其中的 hunspell_en_us。
(1) 下载对应目录中的所有文件。
(2) 将下载的 en_us.affix 和 en_us.dict 这两个文件复制到 PostgreSQL 安装目录下的 share/tsearch_data 子目录中。
(3) 将 hunspell_en_us–*.sql 和 hunspell_en_us.control 文件复制到 PostgreSQL 安装目录下的share/extension 子目录中。
然后执行以下命令：

CREATE EXTENSION hunspell_en_us SCHEMA pg_catalog;
1

查看FTS 的 hunspell 配置库

\dF+ english_hunspell;
Text search configuration "pg_catalog.english_hunspell"
Parser: "pg_catalog.default"
Token 			| Dictionaries
----------------+-------------------------------
asciihword 		| english_hunspell,english_stem
asciiword 		| english_hunspell,english_stem
email 			| simple
file 			| simple
float 			| simple
host 			| simple
hword 			| english_hunspell,english_stem
hword_asciipart | english_hunspell,english_stem
hword_numpart 	| simple
hword_part 		| english_hunspell,english_stem
int 			| simple
numhword 		| simple
numword 		| simple
sfloat 			| simple
uint 			| simple
url 			| simple
url_path 		| simple
version 		| simple
word 			| english_hunspell,english_stem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

查看FTS 的 English 配置库

\dF+ english;
Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
Token 			| Dictionaries
----------------+--------------
asciihword 		| english_stem
asciiword 		| english_stem
email 			| simple
file 			| simple
float 			| simple
host 			| simple
hword 			| english_stem
hword_asciipart | english_stem
hword_numpart 	| simple
hword_part 		| english_stem
int 			| simple
numhword 		| simple
numword 		| simple
sfloat 			| simple
uint 			| simple
url 			| simple
url_path 		| simple
version 		| simple
word 			| english_stem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

如果想了解哪个配置库是系统当前默认使用的库，可以这样查询：

SHOW default_text_search_config;
1

如果希望修改默认配置，可以使用这个命令：

ALTER DATABASE postgresql_book
SET default_text_search_config = 'pg_catalog.english';
1
2

替换后该参数是在 database 级别生效的，但它也可以在服务器级、用户级或者会话级生效。

5.8.2 TSVector原始文本向量

原始文本必须先被向量化然后才能通过 FTS 对其进行全文检索，向量化以后的内容需存储在一个单独的向量字段中，该向量字段使用的数据类型是 tsvector。要从原始文本中生成 tsvector 向量字段，需要先指定使用哪个 FTS 配置库。原始文本经过向量化处理以后会变成一个很精简的单词库，这个库中的每个词都被称为“词素”（lexeme，即不能再拆解的单词或词组，如果拆解将失去原来的含义），同时这个库已经剔除了前面介绍过的停止词。tsvector 字段中记录了每个词素在原始文本中出现的位置。一个词出现的次数越多，其权重值也就越大。这样每个词素都会对应至少一个位置信息，如果出现多次则对应多个位置信息，看起来就像是一个可变长或变短的向量，这也是 tsvector 这个名字的由来。可以使用 to_tsvector 函数来对一个大文本对象进行向量化。默认情况下，该函数会使用系统默认的FTS 配置库，当然你也可以显式指定使用另一个 FTS 配置库。

基于不同的 FTS 配置库将同一段文本向量化以后得到的结果有何差异。

SELECT
c.name,
CASE
WHEN c.name ='default' THEN to_tsvector(f.t)
ELSE to_tsvector(c.name::regconfig,f.t)
END As vect
FROM (
SELECT 'Just dancing in the rain. I like to dance.'::text) As f(t), (
VALUES ('default'),('english'),('english_hunspell'),('simple')
) As c(name);

name 				| vect
--------------------+-----------------------------------------------------------------
default 			| 'danc':2,9 'like':7 'rain':5
english 			| 'danc':2,9 'like':7 'rain':5
english_hunspell 	| 'dance':2,9 'dancing':2 'like':7 'rain':5
simple 				| 'dance':9 'dancing':2 'i':6 'in':3 'just':1 'like':7 'rain':5 'the':4 'to':8
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

可以看出基于四个不同的 FTS 配置库得到的文本向量化结果有何不同。请注意其中的 English 和 Hunspell 配置库剔除了所有像 just 和 to 这样的停止词，此外还按照词典中记录的信息对部分单词进行了规范化处理，比如 dancing 变成了 danc 或者 dance。Simple 配置库不识别词干和停止词，因此其向量化结果中就会出现同一个词的多种时态和那些停止词。

可以看到，to_vector 函数的输出结果中还包含每个词素在原始文本中出现的位置。比如，‘danc’:2,9 就表示 dancing 和 dance 这两个词分别出现在原始文本中的第二个词和第九个词的位置。

要想在你的 database 中支持 FTS 能力，需要在存储原始文本的表上增加一个 tsvector 类型的向量字段，然后通过定时任务定期更新该向量字段或者在表上创建个触发器，一旦原始文本字段发生了修改，则同时更新向量字段。

增加 tsvector 向量字段并设置词素权重

ALTER TABLE film ADD COLUMN fts tsvector;
UPDATE film
SET fts =
setweight(to_tsvector(COALESCE(title,'')),'A') ||
setweight(to_tsvector(COALESCE(description,'')),'B');
CREATE INDEX ix_film_fts_gin ON film USING gin (fts);
1
2
3
4
5
6

对 title 和 description 字段进行了向量化并把结果存入了新增的向量字段。为了使查询更快，我们还在向量字段上创建了一个 GIN 类型的索引。GIN 是无损索引，你也可以对其创建一个 GiST 类型的有损索引。相比 GIN 索引，GiST 索引的特点是原始数据信息可能会丢失并且访问速度会慢一些，但它的构造速度很快而且占用磁盘空间也更小。

为了区分不同词素的重要程度，我们引入了权重（weight）的概念，每个词素都有自己的权重。权重值只能是 A、B、C 和 D 四类中的一种，A 类表示重要程度最高，在搜索结果中也需要排名最靠前。如果我们搜索的关键词命中了 title 中的某个词素，那么我们认为这个搜索结果比从 description 字段抽取出来的词素命中的结果相关性更高，更符合用户的需要。

多个 tsvector 向量可以通过连接运算符||合并为一个新的 tsvector。这样真正执行全文检索时，只需要搜索一个向量字段即可。

能够自动更新向量字段的触发器

CREATE TRIGGER trig_tsv_film_iu
BEFORE INSERT OR UPDATE OF title, description ON film FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(fts,'pg_catalog.english',
title,description);
1
2
3
4

一旦在 title 或 description 字段上执行了 insert 或者 update 操作，则该触发器会被触发，然后会自动完成重新向量化的工作。这个方法有个缺点，就是里面调用的 tsvector_update_trigger 函数不支持设置权重（数据频繁改动时性能不好）。

5.8.3 TSQueries检索条件向量

对于 FTS 或者任何别的文本检索方法来说，都必须具备两个基本元素：一个是被检索的原始文本，一个是检索条件（或者说关键词）。对 FTS 来说，原始文本和检索条件都必须先被向量化然后才能使用。在对检索条件进行向量化处理时，使用的配置库应该与对原始文本向量化时使用的配置库相同。

在 FTS 检索机制中，用 tsquery 类型来表示向量化以后的检索条件。PostgreSQL 提供了若干函数来实现检索条件的向量化处理，包括 to_tsquery、plainto_tsquery 和 phraseto_tsquery。其中phraseto_tsquery 是 PostgreSQL 9.6 新引入的，相比其他两个，该函数会将检索条件中各关键词的顺序也考虑在内。检索条件向量一般是运行时临时生成的，不会放到表中存储下来。

分别基于两个配置库，用 to_tsquery 函数来构造 tsquery 向量

SELECT to_tsquery('business & analytics');
to_tsquery
-----------------
'busi' & 'analyt'

SELECT to_tsquery('english_hunspell','business & analytics');
to_tsquery
--------------------------------
('business' | 'busy') & 'analyt'
1
2
3
4
5
6
7
8
9

这两个例子都是用 business 和 analytics 两个词来进行全文检索，其中的与运算符（&）表示两个关键词必须在目标文本中同时出现才算检索命中。或运算符（|）表示目标文本中必须出现两个词中的至少一个就算检索命中。如果使用的配置库查到其中的某个关键词拥有多个词干，那么最终的向量化结果中会用 | 运算符把这些词干连接到一起呈现。

plainto_tsquery 是 to_tsquery 的变种，它会自动在检索条件的关键词之间加上 & 运算符。用 plainto_tsquery 函数来构造 tsquery 向量

SELECT plainto_tsquery('business analytics');
plainto_tsquery
-----------------
'busi' & 'analyt'
1
2
3
4

to_tsquery 和 plainto_tsquery 仅考虑关键词本身，不考虑其先后位置。因此对这两个函数来说，“business analytics”和“analytics business”得到的 tsquery 结果是一样的。由于无法识别词与词之间的先后顺序，带来的问题就是用户只能搜索单个关键词 PostgreSQL 9.6 中为了解决这个问题而引入了 phraseto_tsquery 函数。phraseto_tsquery 函数的向
量化结果中在词素与词素之间增加了距离运算符，这意味着在检索目标文本中 business 和 analytics 两个词必须以一定顺序出现时才算命中。这样就从功能上实现了从单词搜索到词组搜索的进化。

用 phraseto_tsquery 函数来构造 tsquery 向量

SELECT phraseto_tsquery('business analytics');
phraseto_tsquery
-------------------
'busi' <-> 'analyt'

SELECT phraseto_tsquery('english_hunspell','business analytics');
phraseto_tsquery
---------------------------------------------
'business' <-> 'analyt' | 'busy' <-> 'analyt'
1
2
3
4
5
6
7
8
9

你还可以直接使用类型强转语法将文本串转为 tsquery 数据，这样就无须使用前面介绍的几个函数，语法类似 ‘business & analytics’::tsquery。这样做虽然简单，但也意味着无法利用前述函数提供的功能，检索关键词不会被抽取为词素，只会被简单地原文复制。

多个 tsquery 向量数据可以使用或运算符（||）或者与运算符（&&）连接在一起。tsquery1 || tsquery2 表达式的意思是检索目标文本要么满足 tsquery1 条件，要么符合 tsquery2 条件。tsquery1 && tsquery2 表达式的意思是检索目标文本必须同时满足 tsquery1 和 tsquery2 条件。

多个 tsquery 向量的关联

SELECT plainto_tsquery('business analyst') || phraseto_tsquery('data scientist');
tsquery
-------------------------------------------
'busi' & 'analyst' | 'data' <-> 'scientist'
SELECT plainto_tsquery('business analyst') && phraseto_tsquery('data scientist');
tsquery
--------------------------------------------
'busi' & 'analyst' & ('data' <-> 'scientist')
1
2
3
4
5
6
7
8

tsquery 和 tsvector 这两种数据类型还支持其他一些运算符，比如判定一个向量值是否是另一个向量值的子集等。

5.8.4 使用全文检索

进行 FTS 检索，查出了所有 title 和 description 字段中含有 hunter 这个词，并且还含有
scientist 或者 chef 这两个单词中的一个或者两个。

SELECT left(title,50) As title, left(description,50) as description
FROM film
WHERE fts @@ to_tsquery('hunter & (scientist | chef)') AND title > '';
title 					| description
------------------------+---------------------------------------------------
ALASKA PHANTOM 			| A Fanciful Saga of a Hunter And a Pastry Chef who
CAUSE DATE 				| A Taut Tale of a Explorer And a Pastry Chef who mu
CINCINATTI WHISPERER 	| A Brilliant Saga of a Pastry Chef And a Hunter who
COMMANDMENTS EXPRESS 	| A Fanciful Saga of a Student And a Mad Scientist w
DAUGHTER MADIGAN 		| A Beautiful Tale of a Hunter And a Mad Scientist w
GOLDFINGER SENSIBILITY 	| A Insightful Drama of a Mad Scientist And a Hunter
HATE HANDICAP 			| A Intrepid Reflection of a Mad Scientist And a Pio
INSIDER ARIZONA 		| A Astounding Saga of a Mad Scientist And a Hunter
WORDS HUNTER 			| A Action-Packed Reflection of a Composer And a Mad
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

指定了关键词先后顺序和接近度的 FTS 检索，要求 hunter 在 scientist 或者 chef 之前，而且它们之间必须相差 4 个词。

SELECT left(title,50) As title, left(description,50) as description
FROM film
WHERE fts @@ to_tsquery('hunter <4> (scientist | chef)') AND title > '';
title 				| description
--------------------+---------------------------------------------------
ALASKA PHANTOM 		| A Fanciful Saga of a Hunter And a Pastry Chef who
DAUGHTER MADIGAN 	| A Beautiful Tale of a Hunter And a Mad Scientist w
(2 rows)
1
2
3
4
5
6
7
8

5.8.5 对检索结果进行排序

FTS 支持对检索结果进行排序，具体是通过 ts_rank 和 ts_rank_cd 这两个函数来实现的。ts_rank 函数只考虑检索关键词在目标文本中出现的频率和权重，而 ts_rank_cd（cd 代表 coverage density，即覆盖密度）还考虑了关键词在目标文本中出现的位置。检索条件中的词素在被检索文本中出现的位置越靠近，则该条检索结果的相关度越高，最终排名越靠前。只有当原始文本的 tsvector 向量数据中带有位置标记时，使用 ts_rank_cd 函数才有意义，因为没有位置标记的情况下根本无法计算检索关键词之间的距离，此时该函数的返回值是 0。另外，我们很容易就能想到检索关键词出现的频率也需要根据位置标记才能计算出来。因此，如果被检索的原始文本的 tsvector 向量数据中没有提供位置标记的话，ts_rank 函数就只能依赖权重来排名了。默认情况下，ts_rank 和 ts_rank_cd 函数在计算过程中会将权重值 A、B、C、D 映射为 1.0、0.4、0.2 和 0.1，之后再进行计算。

对检索结果进行排序

SELECT title, left(description,50) As description,
ts_rank(fts,ts)::numeric(10,3) AS r
FROM film, to_tsquery('english','love & (wait | indian | mad)') AS ts
WHERE fts @@ ts AND title > ''
ORDER BY r DESC;

title 			| description 											| r
----------------+-------------------------------------------------------+------
INDIAN LOVE 	| A Insightful Saga of a Mad Scientist And a Mad Sci 	| 0.999
LAWRENCE LOVE 	| A Fanciful Yarn of a Database Administrator And a 	| 0.252
(2 rows)
1
2
3
4
5
6
7
8
9
10
11

假设我们希望只有当检索条件字段出现在 title 字段中时才算命中，那么我们可以将 title 的权重值设置为 1，其他字段的权重值都设为 0。

使用用户自定义的权重值来对检索结果进行排序，请注意：上面输出的第二行结果中的 rank 值都是 0，这是因为该记录的 title 字段内容并不完全满足 tsquery 条件的要求。

SELECT
left(title,40) As title,
ts_rank('{0,0,0,1}'::numeric[],fts,ts)::numeric(10,3) AS r,
ts_rank_cd('{0,0,0,1}'::numeric[],fts,ts)::numeric(10,3) As rcd
FROM film, to_tsquery('english', 'love & (wait | indian | mad )') AS ts
WHERE fts @@ ts AND title > ''
ORDER BY r DESC;

title 			| r 	| rcd
----------------+-------+------
INDIAN LOVE 	| 0.991 | 1.000
LAWRENCE LOVE 	| 0.000 | 0.000
(2 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

如果你对检索性能非常在意，那么我们建议在查询语句中显式地指明 FTS 配置库，而不要依赖默认值。根据 Oleg Bartunov 的博文“Some FTS Tricks”中的介绍，使用 to_tsquery(‘english’,‘social & (science | scientist)’) 这种写法会比使用 to_tsquery(‘social & (science| scientist)’) 这种写法快一倍。

5.8.6 全文检索向量信息的裁减

默认情况下，对原始文本进行向量化处理时会自动加上位置标记（即词素在原始文本中出现的位置）以及可选的权重值（A、B、C、D 四档）信息。但如果你的检索目标是只要匹配了检索条件中的关键词就可以，而完全不关心这些结果中关键词出现的位置、出现的频率以及重要性，那么你完全可以用 strip 函数对 tsvector 向量数据进行裁减，这样可以节省磁盘空间，也可以提升查询速度。

裁减前和裁减后向量数据的比较

SELECT fts
FROM film
WHERE film_id = 1;
'academi':1A 'battl':15B 'canadian':20B 'dinosaur':2A 'drama':5B 'epic':4B
'feminist':8B 'mad':11B 'must':14B 'rocki':21B 'scientist':12B 'teacher':17B

SELECT strip(fts)
FROM film
WHERE film_id = 1;
'academi' 'battl' 'canadian' 'dinosaur' 'drama' 'epic' 'feminist' 'mad'
'must' 'rocki' 'scientist' 'teacher'
1
2
3
4
5
6
7
8
9
10
11

请务必牢记：虽然裁减后的 tsvector 向量数据查询起来更快，占用磁盘空间更少，但很多运算符和函数都不支持与这种裁减后的向量数据配合使用。例如，由于裁减后的向量数据中不含位置标记信息，因此就不可以对其使用距离运算符。

5.8.7 全文检索机制对JSON和JSONB数据类型的支持

PostgreSQL 10 中为 ts_headline 和 to_tsvector 函数新增了可以处理 json 和 jsonb 数据的版本。这两个函数的 json/jsonb 入参版本的使用方式与其 text 入参版本的用法完全相同，唯一值得注意的地方是它们仅处理 json/jsonb 数据中的 value 部分，而不会关注 key 部分以及 json 本身的那些标记符。

对 json/jsonb 数据进行 tsvector 向量化处理

SELECT to_tsvector(person)
FROM persons WHERE id=1;
to_tsvector
----------------------------------------------------------------------------------
'-5083':19 '-6719':13 '-722':12 '-852':18 '619':11,17 'alex':3 'azaleah':25
'brandon':21 'cell':15 'm':23 'ofelia':7 'rafael':5 'sonia':1 'work':9
(1 row)
1
2
3
4
5
6
7

json、jsonb 入参版本的 ts_headline 和 to_tsvector 函数各自还有一个变体，其第一个参数可用于指定用哪个 FTS 配置库，这个特点与 text 入参版本的 ts_headline 和 to_tsvector 函数完全相同。为了更好地利用这些函数的能力，我们推荐的做法是在被检索的 json/jsonb 数据所在的表上先增加 tsvector 向量字段，然后创建触发器在修改时自动生成或者在需要时手工生成向量数据。

ts_headline 函数也有了支持 json/jsonb 入参的版本，该函数的功能是将 json/jsonb 数据中所有命中的文本都标记为 HTML 格式。

对检索命中的文本打上标记

SELECT ts_headline(person->'spouse'->'parents', 'rafael'::tsquery)
FROM persons_b WHERE id=1;
{"father": "<b>Rafael</b>", "mother": "Ofelia"}
(1 row)
1
2
3
4

请注意，上面输出的命中结果前后已打上了 HTML 的 标签。

5.9 自定义数据类型和复合数据类型

composite（也称为 record、row）常用于构建需要转为自定义数据类型的对象或者是作为需要返回多个字段的函数的返回值类型定义。

5.9.1 所有表都有一个对应的自定义数据类型

PostgreSQL 在建表时会自动创建一个与表结构完全相同的自定义数据类型，而且这种类型与其他的原生数据类型在使用上毫无区别。可以在建表时指定某字段为表类型或者表数组类型，也就是说可以把一张表的字段定义为另一张表。

创建嵌套表

CREATE TABLE chickens (id integer PRIMARY KEY);
CREATE TABLE ducks (id integer PRIMARY KEY, chickens chickens[]);
CREATE TABLE turkeys (id integer PRIMARY KEY, ducks ducks[]);
INSERT INTO ducks VALUES (1, ARRAY[ROW(1)::chickens, ROW(1)::chickens]);
INSERT INTO turkeys VALUES (1, array(SELECT d FROM ducks d));
1
2
3
4
5

直接在 ducks 表的一条记录的 chickens 字段中插入了两条 chickens 记录，这种情况下这两条记录的构造不受 chickens 表定义的约束，因此即使它们的主键重复也没关系。

最后看一下得到的 turkeys 记录是什么样子的：

SELECT * FROM turkeys;
output
------------------------
id 	| ducks
----+-----------------------
1 	| {"(1,\"{(1),(1)}\")"}
1
2
3
4
5
6

嵌套表中内嵌的表记录是可以进行修改的。例如，我们要对第一个 turkey 内嵌的第二个 chicken 进行修改，那么可以执行如下操作：

UPDATE turkeys SET ducks[1].chickens[2] = ROW(3)::chickens
WHERE id = 1 RETURNING *;
output
--------------
id | ducks
---+-----------------------
1 | {"(1,\"{(1),(3)}\")"}
1
2
3
4
5
6
7

使用 RETURNING 子句来返回本次更新操作涉及的所有记录。

一个复合类型的记录行或者字段不管其内部结构有多么复杂，都可以被转换为一个 json 或者 jsonb类型的字段。

SELECT id, to_jsonb(ducks) AS ducks_jsonb
FROM turkeys;
id 	| ducks_jsonb
----+------------------------------------------------
1 	| [{"id": 1, "chickens": [{"id": 1}, {"id": 3}]}]
(1 row)
1
2
3
4
5
6

PostgreSQL 内部维护着数据库对象之间的依赖关系。前述 ducks 表的 chickens 字段依赖于 chickens 表，turkeys 表的 ducks 记录依赖于 ducks 表。要想删除 chickens 表有两种方法，要么在 drop 语句中带上 CASCADE 关键字，要么先删除 ducks 表中的 chickens 字段。如果使用前一种方法，那么 ducks 表的 chickens 字段会被自动删除，而且此过程中无告警信息。相应地，turkeys 表的 ducks 字段的定义也将自动跟着改变。

5.9.2 构建自定义数据类型

尽管仅仅通过创建表就可以轻松创建复合数据类型，但有时候我们仍需要从头开始构建自己的数据类型。
例如，使用以下语句可以构建一个复数数据类型：

CREATE TYPE complex_number AS (r double precision, i double precision);
1

可以将此类型作为字段类型定义使用：

CREATE TABLE circuits (circuit_id serial PRIMARY KEY, ac_volt complex_number);
1

可以使用如下语法对这个表进行查询：

SELECT circuit_id, (ac_volt).* FROM circuits;
1

这种语法也可以：

SELECT circuit_id, (ac_volt).r, (ac_volt).i FROM circuits;
1

5.9.3 复合类型中的空值处理

在 ANSI SQL 标准中，两个 NULL 值之间不允许进行“值相等”（即 NULL=NULL）或者“值不等”（即NULL != NULL）判定，这一点经常会给用户带来理解上的困难。当处理 NULL 值时，需要使用 IS NULL、IS NOT NULL 或者 NOT（somevalue IS NULL）这种表达方式。对于基础数据类型来说，something IS NULL 就是 something IS NOT NULL 的反义表达式，但对于复合数据类型来说却并不是这样。

PostgreSQL 在处理 NULL 值时严格遵循 ANSI SQL 标准，其中规定：复合数据类型值的 IS NULL 判定要想成立，其前提是该复合数据类型值的每一个元素都是 NULL，这很合理。但接下来就有点“不太合理”了，复合数据类型值的 IS NOT NULL 判定要想成立，前提是该复合数据类型值的每一个元素都是 NULL，而不是说只需其中任何一个元素不为 NULL 即可。这里特别容易出错，因此请牢记此规则。

5.9.4 为自定义数据类型构建运算符和函数

在构建自定义数据类型后，你自然就需要为其创建相应的函数和运算符。接下来将演示如何为 complex_number 类型创建一个 + 运算符，而创建处理函数的方法将在第 8 章中介绍。

每个运算符都有一个底层实现函数，该函数需要一个或者两个参数，运算符就是这个函数的符号化别名。运算符不仅仅是其底层实现函数的别名，它还可以提供一些可以帮助规划器更好工作的优化信息，规划器借助这些信息可以判定如何使用索引，如何以最低的成本访问数据，以及哪些运算符表达式是等价的。

为 complex_number 创建底层实现函数

CREATE OR REPLACE FUNCTION add(complex_number, complex_number)
RETURNS complex_number AS
$$
SELECT
((COALESCE(($1).r,0) + COALESCE(($2).r,0)),
(COALESCE(($1).i,0) + COALESCE(($2).i,0)))::complex_number;
$$
language sql;
1
2
3
4
5
6
7
8

为 complex_number 类型定义 + 运算符

CREATE OPERATOR + (
PROCEDURE = add,
LEFTARG = complex_number,
RIGHTARG = complex_number,
COMMUTATOR = +
);
1
2
3
4
5
6

然后我们测试一下这个新的 + 运算符：

SELECT (1,2)::complex_number + (3,-10)::complex_number;
1

输出结果是 (4,-8)。