# 其他特性 > 通过完整的 SQL 示例，体验 PostgreSQL 的全文检索、JSON、JSONB 和外部数据包装器。 --- LLMS 索引： [llms.txt](/llms.txt) --- ## 全文检索 {#intro-section-9-1} 全文检索是指搜索用自然语言写成的文档，并按与搜索查询的相关性对结果排序。在最简单、最常见的情况下，查询由一个或多个词组成，相关性则由这些词在文档中出现的频率决定。这大致就是我们在 Google 或 Yandex 搜索引擎中输入短语时发生的事情。不过，SQL 虽然功能强大，却并不总能有效地处理数据。随着大数据的兴起，这一点越来越明显：大数据往往结构不规整，也很难解析。 有许多免费或收费的搜索引擎，可以为整个文档集合建立索引，并提供质量相当不错的搜索。这时，索引这个加速查询的关键搜索工具独立于数据库之外，许多有价值的数据库功能也就无法使用了：数据库同步、事务隔离、通过访问和使用元数据来限定搜索范围、设置访问策略，等等。 面向文档的数据库管理系统通常也有类似的缺点：全文检索功能丰富，但数据安全和同步功能的优先级较低。此外，这类数据库（例如 MongoDB）通常属于 NoSQL 数据库，因此从设计上就缺少 SQL 多年来积累的强大能力。 不过，传统 SQL 数据库系统也内置了全文检索能力。`LIKE` 操作符是标准 SQL 语法的一部分，但它的灵活性往往不够，因此开发者不得不自行扩展 SQL 标准。在 PostgreSQL 中，这些扩展包括比较操作符 `ILIKE`、`~` 和 `~*`。但它们也无法解决所有问题：不考虑词形变化、不适合对结果进行排名，而且速度较慢。 谈到全文检索本身的工具，需要明白，它们远未实现标准化：各个数据库系统都有自己的方法和语法。俄罗斯的 PostgreSQL 用户在这方面有一些优势：PostgreSQL 的全文检索扩展由俄罗斯开发者创建，必要时可以直接联系专家，甚至参加他们的讲座，深入了解底层细节。这里只给出一些简单的示例。 为了了解全文检索的功能，我们将在演示数据库中再建一张表。假设表中保存一位讲师的讲义草稿，按授课主题分成章节： ```psql test=# CREATE TABLE course_chapters( c_no text REFERENCES courses(c_no), ch_no text, ch_title text, txt text, CONSTRAINT pkt_ch PRIMARY KEY(ch_no, c_no) ); ``` ```sql CREATE TABLE ``` 现在为 CS301 和 CS305 两门课程录入前几讲的文本： ```psql test=# INSERT INTO course_chapters( c_no, ch_no, ch_title, txt) VALUES ('CS301', 'I', 'Databases', 'We start our acquaintance with ' || 'the fascinating world of databases'), ('CS301', 'II', 'First Steps', 'Getting more fascinated with ' || 'the world of databases'), ('CS305', 'I', 'Local Networks', 'Here we start our adventurous journey ' || 'through the intriguing world of networks'); INSERT 0 3 ``` 检查结果： ```psql test=# SELECT ch_no AS no, ch_title, txt FROM course_chapters \gx -[ RECORD 1 ]----------------------------------------- no | I ch_title | Databases txt | We start our acquaintance with the fascinating world of databases -[ RECORD 2 ]----------------------------------------- no | II ch_title | First Steps txt | Getting more fascinated with the world of databases -[ RECORD 3 ]----------------------------------------- no | I ch_title | Local Networks txt | Here we start our adventurous journey through the intriguing world of networks ``` 接下来，用传统的 SQL 方法（`LIKE` 操作符）在数据库中查找一些信息： ```psql test=# SELECT ch_no AS no, ch_title, txt FROM course_chapters WHERE txt LIKE '%fascination%' \gx ``` 结果不难猜到：0 行。`LIKE` 操作符无法识别“fascination”与文本中“fascinating”和“fascinated”之间的联系。 下面的查询 ```psql test=# SELECT ch_no AS no, ch_title, txt FROM course_chapters WHERE txt LIKE '%fascinated%' \gx ``` 会返回第 II 章的那一行，却不会返回使用了形容词“fascinating”的第 I 章： ```text -[ RECORD 1 ]----------------------------------------- no | II ch_title | First Steps txt | Getting more fascinated with the world of databases ``` PostgreSQL 提供了 `ILIKE` 操作符，让我们不必考虑字母大小写；否则，还得把大小写的区别考虑进去。当然，也可以使用正则表达式（搜索模式）。编写正则表达式的确很有趣，几乎称得上是一门艺术，但有时我们只是想要一个能把事情办好的工具。因此，给 `course_chapters` 表再加一列，使用一种名为 `tsvector` 的特殊类型： ```psql test=# ALTER TABLE course_chapters ADD txtvector tsvector; test=# UPDATE course_chapters SET txtvector = to_tsvector('english',txt); test=# SELECT txtvector FROM course_chapters \gx ``` ```text -[ RECORD 1 ]----------------------------------------- txtvector | 'acquaint':4 'databas':10 'fascin':7 'start':2 'world':9 -[ RECORD 2 ]----------------------------------------- txtvector | 'databas':8 'fascin':3 'get':1 'world':6 -[ RECORD 3 ]----------------------------------------- txtvector | 'adventur':5 'intrigu':9 'journey':6 'network':12 'start':3 'world':10 ``` 可以看到，这些行发生了变化： - 单词被归约为词根形式，即词位（lexeme）。 - 出现了数字，用来表示单词在文本中的位置。 - 介词被去掉了；连词和其他对搜索不重要的句子成分也会被去掉，这些词称为停用词（stop words）。 如果把章节标题也纳入搜索，并赋予它比正文更高的权重，搜索效果会更好；可以用 `setweight` 函数做到这一点。修改表中的数据： ```psql test=# UPDATE course_chapters SET txtvector = setweight(to_tsvector('english',ch_title),'B') || ' ' || setweight(to_tsvector('english',txt),'D'); UPDATE 3 ``` ```psql test=# SELECT txtvector FROM course_chapters \gx -[ RECORD 1 ]----------------------------------------- txtvector | 'acquaint':5 'databas':1B,11 'fascin':8 'start':3 'world':10 -[ RECORD 2 ]----------------------------------------- txtvector | 'databas':10 'fascin':5 'first':1B 'get':3 'step':2B 'world':8 ``` ```text -[ RECORD 3 ]----------------------------------------- txtvector | 'intrigu':10 'journey':7 'local':1B 'network':2B,13 'start':5 'world':11 ``` 我们为词位分配了相对权重标记 B 和 D，可选标记有 A、B、C、D。具体权重将在编写查询时指定，这样更灵活。 准备就绪，回到搜索。`to_tsquery` 函数与前面见过的 `to_tsvector` 函数类似，它将字符串转换为查询使用的 `tsquery` 数据类型。 ```psql test=# SELECT ch_title FROM course_chapters WHERE txtvector @@ to_tsquery('english','fascination & database'); ch_title ------------- Databases First Steps ( rows) ``` 可以验证，查询 `'fascinated & database'` 及其其他词形变体会返回相同的结果。这里使用了比较操作符 `@@`，它在全文检索中的作用类似于 `LIKE` 在普通搜索中的作用。`@@` 操作符的语法不允许包含空格的自然语言表达式，因此查询中的词用逻辑“与”操作符连接。（译注：这里的限制实际来自 `to_tsquery` 的输入语法；它要求词位之间使用 `&` 等查询操作符。若要处理自然语言文本，可使用 `plainto_tsquery()`。） `english` 参数表示 PostgreSQL 使用的配置。配置定义要接入的词典和解析器；解析器把短语拆成单独的词位。 词典虽然叫作“词典”，但可以完成各种词位转换。例如，默认使用的 snowball 这类简单词干提取词典会把单词归约为不变的部分，使搜索能够忽略查询中的词尾变化。还可以接入其他词典，例如： - `ispell`、`myspell` 或 `hunspell` 等常规词典，能更好地处理词形变化 - 同义词词典 - 词组词典（thesaurus） - `unaccent`，可以去掉字母上的变音符号 分配权重后，就可以对显示的搜索结果排名： ```psql test=# SELECT ch_title, ts_rank_cd('{0.1, 0.0, 1.0, 0.0}', txtvector, q) FROM course_chapters, to_tsquery('english','Databases') q WHERE txtvector @@ q ORDER BY ts_rank_cd DESC; ``` ```text ch_title | ts_rank_cd -------------+------------ Databases | 1.1 First Steps | 0.1 ( rows) ``` 数组 {0.1, 0.0, 1.0, 0.0} 设置权重，是 `ts_rank_cd` 函数的可选参数。默认数组 {0.1, 0.2, 0.4, 1.0} 依次对应 D、C、B、A。单词的权重会影响返回行的排名。 最后一个实验来修改显示格式。假设我们希望在 HTML 页面中用粗体突出显示找到的词。`ts_headline` 函数可以指定突出显示单词所用的符号，并设置一行中显示的最少和最多单词数： ```psql test=# SELECT ts_headline( 'english', txt, to_tsquery('english', 'world'), 'StartSel=, StopSel=, MaxWords=50, MinWords=5' ) FROM course_chapters WHERE to_tsvector('english', txt) @@ to_tsquery('english', 'world'); ``` ```text -[ RECORD 1 ]----------------------------------------- ts_headline | with the fascinating world of databases -[ RECORD 2 ]----------------------------------------- ts_headline | with the world of databases -[ RECORD 3 ]----------------------------------------- ts_headline | through the intriguing world of networks ``` 为了加快全文检索，可以使用不同于常规数据库索引的专用索引：GiST、GIN 和 RUM。不过，与全文检索的许多其他实用功能一样，这些内容超出了本指南的范围。 有关全文检索的更多信息，请参阅文档：[www.postgrespro.com/doc/textsearch]()。 ## 使用 JSON 与 JSONB {#intro-section-9-2} 基于 SQL 的关系型数据库在设计之初就非常重视数据一致性和安全性，当时的数据量远小于今天。NoSQL 数据库出现时，曾引起社区的担忧：不支持严格一致性，加上简单得多的数据结构（最初只是存储键值对），使搜索速度显著提升。它们大量使用并行计算，能够处理前所未有的信息量，而且容易扩展规模。 最初的冲击过后，人们逐渐意识到，这样简单的结构不足以应对大多数实际任务。于是先有了复合键，接着又出现了键组。为了保持竞争力，关系型数据库系统也开始引入通常与 NoSQL 联系在一起的功能。 在关系型数据库中修改数据库结构代价很高，这时新的 JSON 数据类型就派上了用场。它具有类似 XML 的层次结构，最初面向 JavaScript 开发（这也是名称中 JS 的由来），包括 AJAX 应用开发。JSON 的灵活性使开发者可以存储和管理结构动态变化、难以预知的数据，而不必修改数据库结构。 假设需要在 `students` 演示数据库中录入个人信息。我们开展了一次调查，从教授那里收集信息。问卷中有些问题是选答题，另一些问题包含“请自行补充信息”和“其他”字段。如果采用传统方法，那些不符合现有结构的信息就需要新增许多表和列，其中会留下大量空字段。随着数据集增长，甚至可能需要重构整个数据库。（译注：原版称 `students` 为演示数据库；本例的 SQL 显示，`students` 实际是 `test` 数据库中的表。正文名称按原版保留。） 使用 `json` 或 `jsonb` 类型可以解决这个问题。`jsonb` 比 `json` 出现得晚，以紧凑的二进制形式存储数据；与 `json` 不同，它支持索引，有时能将搜索速度提高一个数量级。 创建一张包含 JSON 对象的表： ```psql test=# CREATE TABLE student_details( de_id int, s_id int REFERENCES students(s_id), details json, CONSTRAINT pk_d PRIMARY KEY(s_id, de_id) ); ``` ```psql test=# INSERT INTO student_details (de_id, s_id, details) VALUES (1, 1451, '{ "merits": "none", "flaws": "immoderate ice cream consumption", "status" : "expelled" }'), (2, 1432, '{ "hobbies": { "guitarist": { "band": "Postgressors", "guitars":["Strat","Telec"] } } }' ), (3, 1556, '{ "hobbies": "cosplay", "merits": { "mother-of-five": { "Basil": "m", "Simon": "m", "Lucie": "f", "Mark": "m", "Alex": "unknown" } } }' ); ``` 检查数据是否正确插入。新表中没有学生姓名，为方便查看，使用 `WHERE` 子句连接 `student_details` 和 `students` 两张表： ```psql test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id \gx -[ RECORD 1 ]-------------------------------------- name | Anna details | { "merits": "none", + | "flaws": + | "immoderate ice cream consumption", + | "status" : "expelled" + | } -[ RECORD 2 ]-------------------------------------- name | Victor details | { "hobbies": + | { "guitarist": + | { "band": "Postgressors", + | "guitars":["Strat","Telec"] + | } + | } + | } -[ RECORD 3 ]-------------------------------------- name | Nina details | { "hobbies": "cosplay", + | "merits": + | { "mother-of-five": + | { "Basil": "m", + | "Simon": "m", + | "Lucie": "f", + | "Mark": "m", + | "Alex": "unknown" + | } + | } + | } ``` 假设我们关心的是包含学生优点信息的记录。用特殊操作符 `->>` 访问“merits”键的值： ```psql test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'merits' IS NOT NULL \gx ``` ```text -[ RECORD 1 ]-------------------------------------- name | Anna details | { "merits": "none", + | "flaws": + | "immoderate ice cream consumption", + | "status" : "expelled" + | } -[ RECORD 2 ]-------------------------------------- name | Nina details | { "hobbies": "cosplay", + | "merits": + | { "mother-of-five": + | { "Basil": "m", + | "Simon": "m", + | "Lucie": "f", + | "Mark": "m", + | "Alex": "unknown" + | } + | } + | } ``` 结果中的两条记录分别涉及 Anna 和 Nina 的优点，但这个结果具有误导性，因为 Anna 的优点实际上是“none”（无）。修改查询： ```psql test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'merits' IS NOT NULL AND sd.details ->> 'merits' != 'none'; ``` 新查询只会返回 Nina，她确实有优点。 不过，这种方法并不总是有效。试着查询 Victor 弹奏的吉他： ```psql test=# SELECT sd.de_id, s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'guitars' IS NOT NULL \gx ``` 这个查询不会返回任何结果，因为相应的键值对位于 JSON 层次结构内部，嵌套在更高层的键值对中： ```text name | Victor details | { "hobbies": + | { "guitarist": + | { "band": "Postgressors", + | "guitars":["Strat","Telec"] + | } + | } + | } ``` 要取出吉他列表，可以使用 `#>` 操作符，从“hobbies”开始沿层次结构向下查找： ```psql test=# SELECT sd.de_id, s.name, sd.details #> '{hobbies,guitarist,guitars}' FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details #> '{hobbies,guitarist,guitars}' IS NOT NULL; ``` 可以看出，Victor 是 Fender 的爱好者： ```text de_id | name | ?column? -------+--------+------------------- | Victor | ["Strat","Telec"] ``` `json` 有个小兄弟：`jsonb`。字母“b”表示数据采用二进制而非文本格式存储和组织，这通常能加快搜索。如今，`jsonb` 的使用频率远高于 `json`。 ```psql test=# ALTER TABLE student_details ADD details_b jsonb; ``` ```psql test=# UPDATE student_details SET details_b = to_jsonb(details); test=# SELECT de_id, details_b FROM student_details \gx -[ RECORD 1 ]-------------------------------------- de_id | 1 details_b | {"flaws": "immoderate ice cream consumption", "merits": "none", "status": "expelled"} -[ RECORD 2 ]-------------------------------------- de_id | 2 details_b | {"hobbies": {"guitarist": {"guitars": ["Strat", "Telec"], "band": "Postgressors"}}} -[ RECORD 3 ]-------------------------------------- de_id | 3 details_b | {"hobbies": "cosplay", "merits": {"mother-of-five": {"Basil": "m", "Lucie": "f", "Alex": "unknown", "Mark": "m", "Simon": "m"}}} ``` 除了表示形式不同，值的顺序也变了：缺少补充信息的 Alex 现在排在 Mark 前面。这并不意味着 `jsonb` 不如 `json`，只是它的数据存储方式不同。 与 `json` 相比，`jsonb` 支持更多操作符。其中很有用的一个是“包含”操作符 `@>`，其工作方式类似于 `json` 的 `#>` 操作符。（译注：原版将两者并称为工作方式类似；按 PostgreSQL 17 文档，`@>` 用于判断 JSON 包含关系，`#>` 用于按路径提取 JSON 子对象。正文按原版保留。） 例如，查找提到了 Lucie 的记录，她是那位母亲的五个孩子之一： ```psql test=# SELECT s.name, jsonb_pretty(sd.details_b) json FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> '{"merits":{"mother-of-five":{}}}' \gx ``` ```text -[ RECORD 1 ]------------------------------------- name | Nina json | { + | "merits": { + | "mother-of-five": { + | "Alex": "unknown", + | "Mark": "m", + | "Basil": "m", + | "Lucie": "f", + | "Simon": "m" + | } + | }, + | "hobbies": "cosplay" + | } ``` 这里使用了 `jsonb_pretty()` 函数来格式化 `jsonb` 类型的输出。 也可以使用 `jsonb_each()` 函数展开键值对： ```psql test=# SELECT s.name, jsonb_each(sd.details_b) FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> '{"merits":{"mother-of-five":{}}}' \gx ``` ```text -[ RECORD 1 ]------------------------------------- name | Nina jsonb_each | (hobbies,"""cosplay""") -[ RECORD 2 ]------------------------------------- name | Nina jsonb_each | (merits,"{""mother-of-five"": {""Alex"": ""unknown"", ""Mark"": ""m"", ""Basil"": ""m"", ""Lucie"": ""f"", ""Simon"": ""m""}}") ``` 注意，查询中用空的 `{}` 替代了 Nina 孩子的名字。这种语法让应用开发更加灵活。 更重要的是，`jsonb` 允许创建支持 `@>`、其逆操作符 `<@` 以及许多其他操作符的索引，其中 GIN 索引通常最有效。`json` 类型不支持索引，因此，对于高负载应用，通常建议使用 `jsonb`。（译注：原版列出了 `<@`；PostgreSQL 17 文档对默认 `jsonb_ops` GIN 操作类列出的操作符包括 `?`、`?|`、`?&`、`@>`、`@?` 和 `@@`，未将 `<@` 列入其中。正文按原版保留。） 有关 `json`、`jsonb` 数据类型及相关函数的更多信息，请参阅 PostgreSQL 文档：[postgrespro.com/doc/datatype-json]() 和 [postgrespro.com/doc/functions-json]()。 SQL:2016 标准发布时，其中包含 SQL/JSON Path 语言。Postgres Professional 开发了相应实现，提供了 `jsonpath` 类型，后来合入 PostgreSQL 12。 - `$.a.b.c` 取代了 PostgreSQL 11 及更早版本中必须使用的 `'a'->'b'->'c'` 语法。 - `$` 符号表示当前上下文元素，即需要解析的 JSON 片段。 - `@` 表示过滤表达式中的当前上下文元素。搜索会遍历 `$` 表达式中所有可用的路径。 - `*` 是通配符。在带有 `$` 和 `@` 的表达式中，它表示遵循层次结构的路径上的任意值。 - `**` 是通配符，可以在带有 `$` 或 `@` 的表达式中表示路径的任意部分，而不考虑层次结构。不知道元素的确切嵌套层级时，它很有用。 - `?` 操作符用于创建类似于 `WHERE` 的过滤条件，例如 `$.a.b.c ? (@.x > 10)`。 要用 `jsonb_path_query()` 函数查找 cosplay 爱好者，可以写出以下查询： ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies ? (@ == "cosplay")' ) FROM student_details; ``` ```text s_id | jsonb_path_query ------+------------------ 1556 | "cosplay" (1 row) ``` 这个查询只搜索以“hobbies”键开头的 JSON 分支，检查相应的值是否等于“cosplay”。但如果把“cosplay”换成“guitarist”，查询就不会返回任何结果，因为在表中“guitarist”是键，而不是嵌套元素的值。 查询可以在两个层次上操作：一是在路径表达式中定义搜索范围，二是在过滤表达式中用指定条件匹配结果。因此，同一个目标可以用不同方法实现。 例如，以下查询 ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies.guitarist.band?(@=="Postgressors")' ) FROM student_details; ``` 和下面的查询 ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies.guitarist?(@.band=="Postgressors").band' ) FROM student_details; ``` 会返回相同的结果： ```text s_id | jsonb_path_query ------+------------------ 1432 | "Postgressors" (1 row) ``` 在第一个示例中，我们为“hobbies.guitarist.band”分支中的每一项定义了过滤表达式。查看 JSON 本身可知，这个分支只有一个值：“Postgressors”，所以实际上没什么可过滤掉的。第二个示例的过滤发生在高一层的位置，因此必须在过滤表达式中指定到“乐队”的路径，否则过滤器就找不到任何值。使用这种语法，需要事先知道 JSON 的层次结构。如果不知道呢？ 这时可以使用 `**` 通配符，它非常实用！假设我们不确定“Strat”是什么：高空气球、吉他，还是社会最上层的一员，但需要知道表中到底有没有这个词。以前，这需要在 JSON 文档中进行复杂的搜索，除非把 `jsonb` 转成文本。现在，只需运行以下查询： ```psql test=# SELECT s_id, jsonb_path_exists( details::jsonb, '$.** ? (@ == "Strat")' ) FROM student_details; ``` ```text s_id | jsonb_path_exists ------+------------------- 1451 | f 1432 | t 1556 | f 1451 | f (4 rows) ``` 有关 SQL/JSON Path 功能的更多信息，请参阅文档（[postgrespro.com/doc/datatype-json#DATATYPE-JSONPATH]()）以及文章“JSONPath in PostgreSQL: committing patches and selecting apartments”（在 PostgreSQL 中使用 JSONPath：提交补丁与挑选公寓）（[habr.com/en/company/postgrespro/blog/500440/]()）。 SQL:2016 标准还定义了一些便于处理 JSON 的功能和表达式，它们也在逐步进入 PostgreSQL。这里介绍其中一部分，完整列表请参阅文档：[postgrespro.com/doc/functions-json]()。 回到我们的朋友 Anna、Victor 和 Nina。他们参加了一次调查，了解年轻一代喜欢哪些电视剧，结果保存在一张表中。 ```psql test=# CREATE TABLE tv_series( se_id int, ``` `s_id int REFERENCES students(s_id),` ```text se_details jsonb, CONSTRAINT pk_se PRIMARY KEY(s_id, se_id) ); ``` ```psql test=# INSERT INTO tv_series (se_id, s_id, se_details) VALUES (1, 1451, '{"title": ["Game of Thrones", "Peaky Blinders"]}' ), ``` ```text (2, 1432, '{"title": "Babylon 5"}' ), (3, 1556, '{"title": ["Twin Peaks", "Peaky Blinders"]}' ); ``` 数据库工程师来分析这些结果。他们有一套强大的工具：聚合函数、检查约束，以及各种 `json(b)` 工具。 首先应把数组拆成单独的元素（`unnest`）。可以用 `jsonb_array_elements` 完成，但有一个问题： ```psql text=# SELECT jsonb_array_elements( se_details->'title' ) FROM tv_series; ``` ```text ERROR: cannot extract elements from a scalar ``` 果然，问卷数据录入数据库时不够仔细：学生 1432 对应的数据不是数组，虽然只有一个元素，也应该是数组。用 `IS [NOT] JSON ARRAY` 确认一下： ```psql text=# SELECT se_id, se_details FROM tv_series WHERE se_details->'title' IS NOT JSON ARRAY \gx ``` ```text -[ RECORD 1 ]---------------------------------- se_id | 2 se_details | {"title": "Babylon 5"} ``` 可以用 `jsonb_set` 修正类型，它能够修改 `jsonb` 的结构： ```psql text=# UPDATE tv_series SET se_details = jsonb_set( se_details, '{title}', '["Babylon 5"]' ) WHERE se_id = 2; ``` 现在： ```psql test=# SELECT DISTINCT jsonb_array_elements( se_details->'title' ) FROM tv_series; ``` ```text jsonb_array_elements ---------------------- "Twin Peaks" "Babylon 5" "Peaky Blinders" "Game of Thrones" (4 rows) ``` 这就是最终列表。注意，`IS JSON ARRAY` 与 `IS JSON SCALAR`、`IS JSON OBJECT` 以及更通用的 `IS JSON` 属于同一组谓词。详情请参阅文档：[postgrespro.com/doc/functions-json]()。 ## 与外部系统集成 {#intro-section-9-3} 现实中的应用程序并不孤立，往往需要相互传递数据。可以在应用层实现这种交互，例如使用 Web 服务或交换文件，也可以依靠数据库功能来完成。 PostgreSQL 支持 ISO/IEC 9075-9 标准，即 SQL/MED（外部数据管理）。该标准定义了通过外部数据包装器（foreign data wrapper，FDW）这一专用机制，从 SQL 访问外部数据源的方法。 其思路是像访问普通 PostgreSQL 表中的数据一样访问外部数据。为此需要创建外部表；外部表本身不存放数据，只将所有查询转发给外部数据源。这种方式屏蔽了各个外部数据源的具体差异，方便了应用开发。 创建外部表需要几个步骤。 1. `CREATE FOREIGN DATA WRAPPER` 命令接入用于操作特定数据源的库。 2. `CREATE SERVER` 命令定义外部服务器。通常需要指定主机名、端口号和数据库名等连接参数。 3. `CREATE USER MAPPING` 命令提供用户名映射，因为不同的 PostgreSQL 用户可以用不同外部用户的身份连接同一个外部数据源。 4. `CREATE FOREIGN TABLE` 命令为指定的外部表和视图创建外部表；`IMPORT FOREIGN SCHEMA` 则可以从外部模式导入部分或全部表的定义。 下面介绍 PostgreSQL 如何与最常用的几种数据库集成：Oracle、MySQL、SQL Server，以及 PostgreSQL 自身。首先需要安装访问这些数据库所需的库。 ### 安装扩展 {#intro-subsection-9-3-1} PostgreSQL 发行版包含两个外部数据包装器：`postgres_fdw` 和 `file_fdw`。前者用于访问外部 PostgreSQL 数据库，后者用于访问服务器上的文件。此外，社区还开发和维护了多种库，用于访问许多常见数据库。完整列表见 [pgxn.org/tag/fdw]()。 Oracle、MySQL 和 SQL Server 的外部数据包装器以扩展的形式提供： - Oracle — [github.com/laurenz/oracle\_fdw]() - MySQL — [github.com/EnterpriseDB/mysql\_fdw]() - SQL Server — [github.com/tds-fdw/tds\_fdw]() 按照这些网页上的说明编译并安装扩展。如果一切顺利，就能在可用扩展列表中看到相应的外部数据包装器。例如，查看 `oracle_fdw`： ```psql test=# SELECT name, default_version FROM pg_available_extensions WHERE name = 'oracle_fdw' \gx ``` ```text -[ RECORD 1 ]---+----------- name | oracle_fdw default_version | 1.2 ``` ### Oracle {#intro-subsection-9-3-2} 首先创建扩展，它会添加一个外部数据包装器： ```psql test=# CREATE EXTENSION oracle_fdw; ``` ```sql CREATE EXTENSION ``` 检查相应的包装器是否已经添加： ```psql test=# \dew ``` ```text List of foreign-data wrappers -[ RECORD 1 ]------------------- Name | oracle_fdw Owner | postgres Handler | oracle_fdw_handler Validator | oracle_fdw_validator ``` 下一步是设置外部服务器。在 `OPTIONS` 子句中，必须指定 `dbserver` 选项，给出 Oracle 实例的连接参数：服务器名、端口号和实例名。 ```psql test=# CREATE SERVER oracle_srv FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/orcl'); ``` ```sql CREATE SERVER ``` PostgreSQL 用户 `postgres` 将以 `scott` 的身份连接 Oracle 实例。 ```psql test=# CREATE USER MAPPING FOR postgres SERVER oracle_srv OPTIONS (user 'scott', password 'tiger'); ``` ```sql CREATE USER MAPPING ``` 我们把外部表导入一个独立的模式。先创建模式： ```psql test=# CREATE SCHEMA oracle_hr; ``` ```sql CREATE SCHEMA ``` 接着导入外部表。这里只导入两张常见的表，`dept` 和 `emp`： ```psql test=# IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO (dept, emp) FROM SERVER oracle_srv INTO oracle_hr; ``` ```text IMPORT FOREIGN SCHEMA ``` 注意，Oracle 数据字典以大写形式保存对象名，而 PostgreSQL 系统目录以小写形式保存。在 PostgreSQL 中访问外部数据时，必须用双引号括起大写的 Oracle 模式名，避免它被转换为小写。 查看外部表列表： ```psql test=# \det oracle_hr.* ``` ```text List of foreign tables Schema | Table | Server -----------+-------+------------ oracle_hr | dept | oracle_srv oracle_hr | emp | oracle_srv (2 rows) ``` 现在对外部表执行以下查询，访问外部数据： ```psql test=# SELECT * FROM oracle_hr.emp LIMIT 1 \gx ``` ```text -[ RECORD 1 ]------------------- empno | 7369 ename | SMITH job | CLERK mgr | 7902 hiredate | 1980-12-17 sal | 800.00 comm | deptno | 20 ``` 也可以写入外部数据： ```psql test=# INSERT INTO oracle_hr.dept(deptno, dname, loc) VALUES (50, 'EDUCATION', 'MOSCOW'); ``` ```sql INSERT 0 1 ``` ```psql test=# SELECT * FROM oracle_hr.dept; ``` ```text deptno | dname | loc --------+------------+---------- 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS 30 | SALES | CHICAGO 40 | OPERATIONS | BOSTON 50 | EDUCATION | MOSCOW (5 rows) ``` ### MySQL {#intro-subsection-9-3-3} 为所需的外部数据包装器创建扩展： ```psql test=# CREATE EXTENSION mysql_fdw; ``` ```sql CREATE EXTENSION ``` 用 `host` 和 `port` 参数定义连接外部实例的外部服务器： ```psql test=# CREATE SERVER mysql_srv FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'localhost', port '3306'); CREATE SERVER ``` 我们将以 MySQL 超级用户的身份建立连接： ```psql test=# CREATE USER MAPPING FOR postgres SERVER mysql_srv OPTIONS (username 'root', password 'p@ssw0rd'); CREATE USER MAPPING ``` 这个包装器支持 `IMPORT FOREIGN SCHEMA` 命令，不过也可以手动创建外部表： ```psql test=# CREATE FOREIGN TABLE employees ( emp_no int, birth_date date, first_name varchar(14), last_name varchar(16), gender varchar(1), hire_date date) SERVER mysql_srv OPTIONS (dbname 'employees', table_name 'employees'); CREATE FOREIGN TABLE ``` 检查结果： ```psql test=# SELECT * FROM employees LIMIT 1 \gx -[ RECORD 1 ]---------- emp_no | 10001 birth_date | 1953-09-02 first_name | Georgi last_name | Facello gender | M hire_date | 1986-06-26 ``` 和 Oracle 包装器一样，`mysql_fdw` 同时支持读写操作。 ### SQL Server {#intro-subsection-9-3-4} 为所需的外部数据包装器创建扩展： ```psql test=# CREATE EXTENSION tds_fdw; CREATE EXTENSION ``` 创建外部服务器： ```psql test=# CREATE SERVER sqlserver_srv FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'localhost', port '1433', database 'AdventureWorks'); CREATE SERVER ``` 所需的连接信息相同，仍要提供主机名、端口号和数据库名。不过，`OPTIONS` 子句使用的参数与 `oracle_fdw` 和 `mysql_fdw` 不同。 我们将以 SQL Server 超级用户的身份建立连接： ```psql test=# CREATE USER MAPPING FOR postgres SERVER sqlserver_srv OPTIONS (username 'sa', password 'p@ssw0rd'); CREATE USER MAPPING ``` 为外部表创建一个独立的模式： ```psql test=# CREATE SCHEMA sqlserver_hr; CREATE SCHEMA ``` 将整个 `HumanResources` 模式导入新建的 PostgreSQL 模式： ```psql test=# IMPORT FOREIGN SCHEMA HumanResources FROM SERVER sqlserver_srv INTO sqlserver_hr; IMPORT FOREIGN SCHEMA ``` 可以用 `\det` 命令显示导入的表，也可以运行以下查询，从系统目录中查找它们： ```psql test=# SELECT ft.ftrelid::regclass AS "Table" FROM pg_foreign_table ft; Table ---------------------------------------- sqlserver_hr.Department sqlserver_hr.Employee sqlserver_hr.EmployeeDepartmentHistory sqlserver_hr.EmployeePayHistory sqlserver_hr.JobCandidate sqlserver_hr.Shift (6 rows) ``` 对象名区分大小写，因此在 PostgreSQL 查询中应使用双引号括起来： ```psql test=# SELECT "DepartmentID", "Name", "GroupName" FROM sqlserver_hr."Department" LIMIT 4; DepartmentID | Name | GroupName --------------+-------------+------------------------- 1 | Engineering | Research and Development 2 | Tool Design | Research and Development 3 | Sales | Sales and Marketing 4 | Marketing | Sales and Marketing (4 rows) ``` 目前，`tds_fdw` 只支持读取，不允许写入。 ### PostgreSQL {#intro-subsection-9-3-5} 创建扩展和包装器： ```psql test=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION ``` 这次要连接同一服务器实例中的另一个数据库，因此创建外部服务器时只需提供 `dbname` 参数，其他参数（例如 `host`、`port` 等）可以省略。 ```psql test=# CREATE SERVER postgres_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'demo'); CREATE SERVER ``` 在同一个数据库集簇内创建用户映射时，不必指定密码：（译注：本例使用 `postgres` 超级用户；是否需要密码取决于用户映射、认证配置和连接方式，不能仅凭位于同一数据库集簇就断定无需密码。） ```psql test=# CREATE USER MAPPING FOR postgres SERVER postgres_srv OPTIONS (user 'postgres'); CREATE USER MAPPING ``` 导入 `bookings` 模式中的所有表和视图： ```psql test=# IMPORT FOREIGN SCHEMA bookings FROM SERVER postgres_srv INTO public; IMPORT FOREIGN SCHEMA ``` 检查结果： ```psql test=# SELECT * FROM bookings LIMIT 3; ``` ```text book_ref | book_date | total_amount ----------+------------------------+-------------- 000004 | 2015-10-12 14:40:00+03 | 55800.00 00000F | 2016-09-02 02:12:00+03 | 265700.00 000010 | 2016-03-08 18:45:00+03 | 50900.00 000012 | 2017-07-14 09:02:00+03 | 37900.00 000026 | 2016-08-30 11:08:00+03 | 95600.00 (5 rows) ``` 有关 `postgres_fdw` 的更多信息，请参阅文档：[postgrespro.com/doc/postgres-fdw]()。 外部数据包装器还值得一提，因为社区认为它们是 PostgreSQL 内置分片功能的基础。分片与分区类似，都是按某种条件把表拆成独立存储的多个部分。区别在于，分区保存在同一台服务器上，分片则位于不同服务器。PostgreSQL 很早就支持分区。从版本 10 开始，分区机制一直在积极发展，已经加入许多实用功能：声明式语法、动态分区剪枝、并行操作支持以及其他多项改进。也可以把外部表用作分区，这实际上就把分区变成了分片。 不过，要让分片完全实用，仍有大量工作要做： - 无法保证一致性：访问外部数据时使用的是各自独立的本地事务，而不是单一的分布式事务。 - 无法在不同服务器上复制同一份数据来提高容错性。 - 在各分片上创建表以及创建相应外部表的所有操作，都必须手动完成。 Postgres Professional 开发的新工具 Shardman 已经解决了这些问题（[postgrespro.ru/products/shardman]()）。 发行版还包含另一个用于访问 PostgreSQL 数据库的扩展：`dblink`。它允许显式管理连接（连接和断开）、执行查询，并异步获取结果：[postgrespro.com/doc/dblink]()。