PHP小码农

Appearance

to_tsvector / to_tsquery / plainto_tsquery ​

上一节讲清了 tsvector 与 tsquery 两个类型长什么样，本节讲怎么把「原始文本」变成这两个类型——也就是全部转换函数（conversion functions）。

转换函数是全文搜索里唯一与用户输入直接打交道的地方，也是安全问题与体验问题的集中爆发点：选错函数，用户输入一个 & 就可能让整条 SQL 报错；配置名写错，索引直接失效。本节把五个核心函数的行为边界逐条拆开。

前置知识

阅读本节前，建议先了解：tsvector 与 tsquery 详解

基础概念 ​

文档侧与查询侧 ​

全文搜索的转换分两条链路，两端必须使用同一个配置：

text
   文档侧（对文档列）                       查询侧（处理用户输入）
   'The Fat Rats'                           'Fat & Rats'
        │ to_tsvector('english', ...)            │ to_tsquery('english', ...)
        ▼                                        │ plainto_tsquery / phraseto_tsquery
   'fat':2 'rat':3    ◄────  @@  ────►          │ websearch_to_tsquery
                                          'fat' & 'rat'

文档侧只有 to_tsvector 一个函数；查询侧有四个函数，差异只在「如何处理用户输入的符号」。这个不对称是理解本节的关键。

五个核心函数速查 ​

函数方向输入里的操作符是否生效遇到语法错误
to_tsvector文档侧不适用（文档没有操作符）不适用
to_tsquery查询侧生效（& | ! <-> :*）抛异常
plainto_tsquery查询侧不生效（全部当普通词）不抛（自动丢弃标点）
phraseto_tsquery查询侧不生效（词之间用 <-> 连接）不抛
websearch_to_tsquery查询侧部分生效（"..."、or、-）永不抛

转换过程中的四个阶段 ​

无论哪个转换函数，内部都走同一条流水线：

text
   1. 解析器（parser）        把文本切成 token，并给每个 token 标类型
        │                     asciiword / word / numword / email / url / blank ...
        ▼
   2. 词典（dictionary）      按 token 类型查配置里注册的词典列表
        │                     第一个「认识」这个 token 的词典给出词位
        ▼
   3. 归一化（normalize）     词干化（rats → rat）、同义词映射、大小写折叠
        │
        ▼
   4. 组装                   去重、排序、记录位置（文档侧）或生成表达式树（查询侧）

第 2 步「哪些 token 类型查哪些词典」由文本搜索配置（configuration）决定，第 3 步的具体规则由词典决定。这两者的细节在后面的全文搜索配置与词典一节展开，本节只关注函数层的行为。

配置名省略时会发生什么 ​

所有转换函数的 config 参数都是可选的。to_tsvector('The Fat Rats') 省略配置时走会话参数 default_text_search_config（默认值 pg_catalog.english，可用 SHOW default_text_search_config 查看，也可在 postgresql.conf 里改或按会话 SET）：

sql
SET default_text_search_config = 'public.pg';

在生产代码里永远显式指定配置名，理由有两个：

  1. 省略配置的 to_tsvector 是 STABLE 而不是 IMMUTABLE，不能用于表达式索引（详见下文「单参数 vs 双参数」）。
  2. 配置一旦被改（或 dump/restore 到不同环境），省略配置的查询结果会静默变化。

语法与用法 ​

to_tsvector ​

text
to_tsvector([ config regconfig, ] document text)  returns tsvector
to_tsvector([ config regconfig, ] document json)  returns tsvector
to_tsvector([ config regconfig, ] document jsonb) returns tsvector
sql
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
text
                        to_tsvector
-------------------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
(1 row)

官方这个例子把四个阶段全体现了：a、on、it 是停用词被丢弃；rats 被词干化为 rat；- 的 token 类型没有配置任何词典所以被忽略；位置编号照常递增，所以 fat 有两个位置 2,11、ate 的位置是 9 而不是 7。

to_tsquery ​

text
to_tsquery([ config regconfig, ] querytext text) returns tsquery

to_tsquery 要求输入已经符合 tsquery 的语法——单个 token 之间必须用 &、|、!、<-> 连接，可以用括号分组。

sql
SELECT to_tsquery('english', 'The & Fat & Rats');
text
   to_tsquery
---------------
 'fat' & 'rat'
(1 row)

注意输入是 The & Fat & Rats（带 &），而不是 The Fat Rats——后者会抛语法错误。to_tsquery 是唯一支持权重标签与前缀匹配的输入函数：

sql
-- 限制只匹配权重 A 或 B 的词位
SELECT to_tsquery('english', 'Fat | Rats:AB') AS weighted;

-- 前缀匹配；注意 :*A 与 :A* 都合法，顺序无关
SELECT to_tsquery('supern:*A & star:A*B') AS prefixed;
text
     weighted      |        prefixed
-------------------+-------------------------
 'fat' | 'rat':AB  | 'supern':*A & 'star':*AB
(1 row)

to_tsquery 还接受单引号短语，这主要用于同义词词典（thesaurus）可能对短语触发的场景。下例中 thesaurus 词典里有规则 supernovae stars : sn，所以整条短语被替换成了词位 sn：

sql
SELECT to_tsquery('''supernovae stars'' & !crab') AS phrase_q;  -- 'sn' & !'crab'

plainto_tsquery ​

text
plainto_tsquery([ config regconfig, ] querytext text) returns tsquery

把一段没有任何格式的普通文本转成 tsquery，在保留下来的词之间插入 &：

sql
SELECT plainto_tsquery('english', 'The Fat Rats') AS q1,
       plainto_tsquery('english', 'The Fat & Rats:C') AS q2;
text
       q1        |           q2
-----------------+-------------------------
 'fat' & 'rat'   | 'fat' & 'rat' & 'c'
(1 row)

q2 是理解规则的关键：plainto_tsquery 不识别 tsquery 操作符、权重标签、前缀标签，输入里所有标点都被当作分隔符丢弃，于是 & 和 :C 消失，只剩下 fat、rat、c 三个词。

推论：plainto_tsquery 永远不会因为用户输入而报错，但也永远无法表达「或」「非」「短语」。

phraseto_tsquery ​

text
phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery

与 plainto_tsquery 行为几乎一样，只改两处：

  1. 词之间插入的是 <->（FOLLOWED BY）而不是 &。
  2. 停用词不是简单丢弃，而是用 <N> 把它的位置占住。
sql
SELECT phraseto_tsquery('english', 'The Fat Rats') AS q1,
       phraseto_tsquery('english', 'The Cat and Rats') AS q2,
       phraseto_tsquery('english', 'The Fat & Rats:C') AS q3;
text
        q1         |      q2       |             q3
-------------------+---------------+----------------------------
 'fat' <-> 'rat'   | 'cat' <2> 'rat' | 'fat' <-> 'rat' <-> 'c'
(1 row)

q2 里 and 是停用词，它被丢弃但位置被保留，所以 cat 与 rat 之间不是紧邻，而是 <2>。这一点正是上一节「停用词会占位」的直接应用。

和 plainto_tsquery 一样，它也不识别操作符、权重、前缀标签——q3 里的 & 与 :C 同样被当成标点丢掉。

websearch_to_tsquery ​

text
websearch_to_tsquery([ config regconfig, ] querytext text) returns tsquery

用「网页搜索框」的语法解析输入，永远不会抛语法错误。支持的语法只有四条：

输入形式转换结果相当于
不带引号的文本词之间用 & 连接plainto_tsquery
"带引号的文本"词之间用 <-> 连接phraseto_tsquery
OR（单独的词）|无
-（连字符前缀）!无

其他标点一律忽略。官方示例：

sql
SELECT websearch_to_tsquery('english', 'The fat rats')                AS q1,
       websearch_to_tsquery('english', '"supernovae stars" -crab')    AS q2,
       websearch_to_tsquery('english', '"sad cat" or "fat rat"')      AS q3,
       websearch_to_tsquery('english', 'signal -"segmentation fault"') AS q4;
text
       q1        |               q2                     |           q3            |                q4
-----------------+----------------------------------------+-------------------------+---------------------------------------
 'fat' & 'rat'   | 'supernova' <-> 'star' & !'crab'        | 'sad' <-> 'cat' | 'fat' <-> 'rat' | 'signal' & !( 'segment' <-> 'fault' )
(1 row)

注意 q4 中 - 作用于整个短语，生成了 !( ... )；这就是「排除短语」的标准写法。

容错能力也是它的一大卖点，下面这条输入几乎全是垃圾，但函数只返回了能识别的两个词位，没有报错：

sql
SELECT websearch_to_tsquery('english', '""" )( dummy \\ query <->') AS q;
text
     q
--------------
 'dummi' & 'queri'
(1 row)

四个 tsquery 函数横向对比 ​

对同一段输入 The Cat and Rats：

函数结果语义
to_tsquery抛异常输入不符合 tsquery 语法
plainto_tsquery'cat' & 'rat'两个词都出现即可
phraseto_tsquery'cat' <2> 'rat'cat 之后隔一个词出现 rat
websearch_to_tsquery'cat' & 'rat'同 plainto_tsquery

对上表可以总结出一句工程结论：只有 websearch_to_tsquery 的结果符合用户预期。 对 "fat rat" or cat -dog 这类真实输入，plainto_tsquery 会把 or 也当成必须匹配的词，phraseto_tsquery 更要求所有词严格相邻，都不可用。

详细说明 ​

配置参数 config 的写法规则 ​

config 参数的类型是 regconfig（pg_ts_config 的 OID 别名类型），因此它的输入规则与 SQL 标识符一致：

  • 不带引号时，大写会被折叠成小写：'English' 等价于 'english'。
  • 双引号内的内容保留大小写：'"English"' 会去找一个真的叫 English 的配置（通常不存在）。
  • 可以带 schema 限定：'public.pg'。
sql
SELECT to_tsvector('English', 'The Fat Rats')       AS folded,    -- 等价于 english
       to_tsvector('public.english', 'The Fat Rats') AS qualified, -- 结果同上
       get_current_ts_config()                       AS cfg;       -- 当前会话配置
text
    folded     |  qualified     |   cfg
---------------+----------------+---------
 'fat':2 'rat':3 | 'fat':2 'rat':3 | english
(1 row)

推荐始终写 schema 限定名（内置配置写 pg_catalog.english，自定义写 public.xxx），这样即使在触发器或索引定义里也不会受 search_path 影响。官方触发器示例就是这么写的：tsvector_update_trigger(tsv, 'pg_catalog.english', title, body)。

单参数 vs 双参数：immutable 的关键差异 ​

这是本节最重要的工程结论。

函数签名易变性能否用于表达式索引
to_tsvector(regconfig, text)IMMUTABLE能
to_tsvector(text)STABLE不能

原因：单参数版本依赖 default_text_search_config 这个会话级 GUC，同一个表达式在不同会话里可能算出不同的 tsvector，因此 PostgreSQL 不允许把它写进索引定义（会报 functions in index expression must be marked IMMUTABLE）。

验证易变性：

sql
SELECT p.proname,
       p.pronargs,
       p.provolatile,
       CASE p.provolatile WHEN 'i' THEN 'IMMUTABLE'
                          WHEN 's' THEN 'STABLE'
                          WHEN 'v' THEN 'VOLATILE' END AS volatility
FROM pg_proc p
WHERE p.proname = 'to_tsvector'
ORDER BY p.pronargs;
text
  proname   | pronargs | provolatile | volatility
------------+----------+-------------+------------
 to_tsvector |        1 | s           | STABLE
 to_tsvector |        2 | i           | IMMUTABLE
(2 rows)

因此建表达式索引时必须用双参数版本：

sql
-- 正确：双参数，IMMUTABLE，可以作为索引表达式
CREATE INDEX idx_articles_fts ON articles
USING gin (to_tsvector('english', body));

-- 错误！单参数不是 IMMUTABLE，建索引会失败
-- CREATE INDEX idx_articles_bad ON articles
-- USING gin (to_tsvector(body));
-- ERROR:  functions in index expression must be marked IMMUTABLE

对应的查询也必须写成同样的双参数形式，索引才会被使用：

sql
-- 能走索引（表达式与索引定义逐字一致）
SELECT id FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('english', $1);

-- 不能走索引（单参数版本，表达式不同）
SELECT id FROM articles WHERE to_tsvector(body) @@ to_tsquery('english', $1);

setweight：给词位打权重 ​

text
setweight(vector tsvector, weight "char")          returns tsvector
setweight(vector tsvector, weight "char", lexemes text[]) returns tsvector

第二参数只能是 A、B、C、D 四个字母之一。D 是默认值，所以输出时不会显示：

sql
SELECT setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') AS with_a,
       setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'D') AS with_d;
text
             with_a             |         with_d
--------------------------------+-------------------------
 'cat':3A 'fat':2A,4A 'rat':5A  | 'cat':3 'fat':2,4 'rat':5B
(1 row)

注意 with_d 的结果：setweight(..., 'D') 把 rat 的 B 权重改成了 D，而 D 不显示；cat、fat 本来就是 D，所以看起来「没变化」。

权重是打在位置上而不是词位上，官方特别强调了这一点：

Note that weight labels apply to positions, not lexemes. If the input vector has been stripped of positions then setweight does nothing.

也就是说 setweight(strip('fat:2,4 cat:3'::tsvector), 'A') 会原样返回 'cat' 'fat'——位置信息已丢，权重无处可挂。

setweight 的典型用法是把结构化文档的不同字段打上不同权重，再用 || 拼起来：

sql
SELECT setweight(to_tsvector('english', coalesce(title, '')),   'A') ||
       setweight(to_tsvector('english', coalesce(keyword, '')), 'B') ||
       setweight(to_tsvector('english', coalesce(body, '')),    'D') AS fts
FROM articles
WHERE id = 1;

三参数版本可以只给指定的词位打权重，词位字符串按原样匹配、不做归一化：

sql
SELECT setweight('fat:2,4 cat:3 rat:5,6B'::tsvector, 'A', '{cat,rat}') AS selective;
text
            selective
-----------------------------------
 'cat':3A 'fat':2,4 'rat':5A,6A
(1 row)

fat 不在 {cat,rat} 里，所以保持原样（无权重）。

strip / length / ts_delete / ts_filter ​

这四个函数都作用于已有的 tsvector，用于检查与裁剪。

text
strip(vector tsvector)            returns tsvector
length(vector tsvector)           returns integer
ts_delete(vector tsvector, lexeme text)      returns tsvector
ts_delete(vector tsvector, lexemes text[])   returns tsvector
ts_filter(vector tsvector, weights "char"[]) returns tsvector
sql
SELECT length('fat:2,4 cat:3 rat:5A'::tsvector) AS n_lexemes,          -- 3
       strip('fat:2,4 cat:3 rat:5A'::tsvector)  AS stripped,           -- 'cat' 'fat' 'rat'
       ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat') AS del_one,  -- 'cat':3 'rat':5A
       ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat']) AS del_many;
text
 n_lexemes |   stripped        |      del_one      | del_many
-----------+-------------------+-------------------+-----------
         3 | 'cat' 'fat' 'rat' | 'cat':3 'rat':5A  | 'cat':3
(1 row)

ts_delete 删掉整个词位（连同它的所有位置与权重）；传入数组则一次删多个。

ts_filter 按权重筛选，weights 数组的字母不分大小写（数组元素类型是 "char"）：

sql
-- cat 位置 7 带权重 c 被筛掉，位置 3 的 B 保留
SELECT ts_filter('fat:2,4 cat:3b,7c rat:5A'::tsvector, '{a,b}') AS only_ab;
text
        only_ab
----------------------
 'cat':3B 'rat':5A
(1 row)

array_to_tsvector 与 tsvector_to_array ​

text
array_to_tsvector(text[])  returns tsvector
tsvector_to_array(tsvector) returns text[]

这两个函数是 tsvector 与应用层之间的双向桥梁：

sql
SELECT array_to_tsvector('{fat,cat,rat}'::text[]) AS to_vec,
       tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector) AS to_arr;
text
    to_vec      |   to_arr
----------------+-------------
 'cat' 'fat' 'rat' | {cat,fat,rat}
(1 row)

两条硬性规则：array_to_tsvector 把数组元素原样当作词位，不做任何归一化，所以元素必须已经是归一化后的形式；数组元素不能是空字符串或 NULL，否则报错 lexeme array may not contain nulls。

tsvector_to_array 输出的是排序并去重后的词位（{cat,fat,rat} 而不是输入顺序），因为 tsvector 内部本身就是有序去重的。

这两个函数在「从外部系统导入已分词结果」时很有用：

sql
-- 把分词服务返回的词数组写入 tsvector 列
UPDATE articles SET fts = array_to_tsvector($1::text[]) WHERE id = $2;

按词位展开：unnest ​

unnest 有一个针对 tsvector 的重载，把每个词位展开成一行：

text
unnest(tsvector) returns setof record(lexeme text, positions smallint[], weights text)
sql
SELECT * FROM unnest('cat:3 fat:2,4 rat:5A'::tsvector);
text
 lexeme | positions | weights
--------+-----------+---------
 cat    | {3}       | {D}
 fat    | {2,4}     | {D,D}
 rat    | {5}       | {A}
(3 rows)

这是唯一被官方推荐的、读取 tsvector 内部内容的正确方式：不要用正则去解析 ::text 输出，格式细节可能随版本变化。一个实际用法是统计某个文档里各词位的出现次数：

sql
SELECT u.lexeme, cardinality(u.positions) AS freq
FROM articles, unnest(articles.fts) u
WHERE articles.id = 1
ORDER BY freq DESC
LIMIT 10;

json / jsonb 文档转换 ​

to_tsvector 直接支持 json 与 jsonb 入参，会递归取出所有字符串值并拼接成一个向量：

sql
SELECT to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::json)  AS from_json,
       to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::jsonb) AS from_jsonb;
text
    from_json    |    from_jsonb
-----------------+------------------
 'dog':5 'fat':2 'rat':3 | 'dog':1 'fat':4 'rat':5
(1 row)

两者的位置完全不同，官方解释了原因：

Beware that "document order" of the fields of a JSON object is implementation-dependent when the input is jsonb.

json 保留原始书写顺序，而 jsonb 是解析后的二进制结构（键有排序），顺序不可依赖，不要依赖 JSON 转换后的位置做短语匹配。位置生成规则是「每两个字符串值之间视作存在一个停用词」——这也解释了为什么 dog 在 from_json 里是位置 5 而不是 2。

如果需要选择性地转换，用带 filter 的版本。filter 必须是 jsonb 数组，元素取自 "string"、"numeric"、"boolean"、"key"、"all"：

text
json_to_tsvector ([config regconfig,] document json,  filter jsonb) returns tsvector
jsonb_to_tsvector([config regconfig,] document jsonb, filter jsonb) returns tsvector
sql
SELECT json_to_tsvector('english', '{"a": "The Fat Rats", "b": 123}'::json,
                        '["string", "numeric"]') AS s_and_n,   -- '123':5 'fat':2 'rat':3
       json_to_tsvector('english', '{"cat": "The Fat Rats", "dog": 123}'::json,
                        '"all"')                 AS everything; -- '123':9 'cat':1 'dog':7 'fat':4 'rat':5
text
     s_and_n       |                 everything
-------------------+---------------------------------------------
 '123':5 'fat':2 'rat':3 | '123':9 'cat':1 'dog':7 'fat':4 'rat':5
(1 row)

第二个结果用了 "all"，把键名（cat、dog）也算进去了，所以位置编号不同。注意 "all" 既可以写成 '["all"]' 也可以直接写成 '"all"'。

tsquery_phrase 与 ts_rewrite ​

tsquery_phrase 用函数形式构造短语查询，等价于 <-> 与 <N> 操作符：

sql
SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))    AS adjacent, -- 'fat' <-> 'cat'
       tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10) AS near10;  -- 'fat' <10> 'cat'

它的价值在于参数可以动态计算：distance 可以是表达式，而 <N> 操作符的 N 必须写在查询文本里。

ts_rewrite 做「查询改写」——把查询里的某个子查询替换成另一个，是 tsquery 版的字符串替换。它有两种形式：三参数版做一次性替换，两参数版接受一条返回两列 tsquery 的 SELECT 语句当规则表。

sql
-- 三参数：把 'a' 替换成 'foo|bar'
SELECT ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery) AS rewritten;
text
     rewritten
-------------------
 'b' & ( 'foo' | 'bar' )
(1 row)
sql
-- 两参数：规则表逐行应用
CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
INSERT INTO aliases VALUES (to_tsquery('supernovae'), to_tsquery('supernovae|sn'));
SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases') AS rewritten;
-- 结果：'crab' & ( 'supernova' | 'sn' )

规则表可以直接 UPDATE，不需要重建索引——这是 ts_rewrite 相对于 thesaurus 词典的最大优势（改 thesaurus 必须重建索引才生效）。规则很多时改写会变慢，官方建议先用包含判断过滤候选：

sql
SELECT ts_rewrite('a & b'::tsquery,
       'SELECT t, s FROM aliases WHERE ''a & b''::tsquery @> t');

ts_stat：文档统计与停用词候选挖掘 ​

text
ts_stat(sqlquery text [, weights text])
  returns setof record(word text, ndoc integer, nentry integer)

sqlquery 必须是一条返回单列 tsvector 的 SQL 文本。三个输出列：word 为词位，ndoc 为该词位出现在多少个文档中，nentry 为该词位的总出现次数。

sql
-- 找出出现最多的 10 个词（也是停用词候选）
SELECT * FROM ts_stat('SELECT fts FROM articles')
ORDER BY nentry DESC, ndoc DESC, word LIMIT 10;

第二参数可以只看特定权重，字符串字母不区分大小写：ts_stat('SELECT fts FROM articles', 'ab') 只统计权重 A 或 B 的出现（比如只看标题与关键词）。

这个函数是调优配置的第一手工具：ndoc 接近总文档数的词就是停用词候选；被切碎的词位（比如本该是一个词的被拆成两个）则说明分词或词典配置有问题。

调试用转换：ts_debug / ts_parse / ts_token_type / ts_lexize ​

这四个函数不是日常搜索用的，但排查「为什么搜不到」时必须用。

ts_debug 展示每个 token 的完整处理过程：

text
ts_debug([config regconfig,] document text)
  returns setof record(alias text, description text, token text,
                       dictionaries regdictionary[], dictionary regdictionary,
                       lexemes text[])
sql
SELECT alias, token, dictionary, lexemes
FROM ts_debug('english', 'The Brightest supernovaes');
text
   alias   |    token    |  dictionary   |  lexemes
-----------+-------------+---------------+------------
 asciiword | The         | english_stem  | {}
 blank     |             |               |
 asciiword | Brightest   | english_stem  | {bright}
 blank     |             |               |
 asciiword | supernovaes | english_stem  | {supernova}
(5 rows)

lexemes 为 {} 表示该 token 被识别为停用词，为 NULL 表示没有词典认识它。

另外三个调试函数：ts_parse(parser_name, document) 只看解析器切出的 token 类型、不做词典处理；ts_token_type(parser_name) 列出解析器认识的全部 token 类型；ts_lexize(dict, token) 单独测试一个词典对一个 token 的处理。

ts_lexize 有一个易踩的坑官方专门提示了：它接收的是单个 token而不是文本，所以用来测 thesaurus 词典会失败：ts_lexize('thesaurus_astro', 'supernovae stars') 返回 NULL。要测短语类词典，改用 plainto_tsquery 或 to_tsvector。

实战示例 ​

示例一：为文章表建立可索引的 tsvector ​

需求：标题权重 A、关键词权重 B、正文权重 D，全部用 english 配置，并通过生成列自动维护。

sql
CREATE TABLE articles (
    id          bigserial PRIMARY KEY,
    title       text NOT NULL,
    keyword     text,
    body        text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    -- 生成列：PG12+ 支持 STORED，自动随源列更新
    fts tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')),   'A') ||
        setweight(to_tsvector('english', coalesce(keyword, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(body, '')),    'D')
    ) STORED
);

CREATE INDEX idx_articles_fts ON articles USING gin (fts);
text
CREATE TABLE
CREATE INDEX

插入数据并验证权重是否正确落地：

sql
INSERT INTO articles (title, keyword, body)
VALUES ('PostgreSQL Full Text Search',
        'gin, tsvector',
        'PostgreSQL provides tsvector and tsquery for full text search.');

SELECT id, fts FROM articles;
text
 id |                                                          fts
----+---------------------------------------------------------------------------------------------------------------
  1 | 'gin':5B 'postgresql':1A,9 'provid':10 'queri':12 'search':14 'text':13 'tsqueri':11 'tsvector':4B,8
(1 row)

权重标签正确区分了三个字段的来源，而 postgresql 同时出现在标题（权重 A，位置 1）与正文（权重 D，位置 9）——同一词位的不同位置可以有不同权重。

示例二：用户搜索框的完整处理流程 ​

需求：把用户原始输入安全地转成 tsquery，且空查询要有兜底。

sql
-- ① 空查询短路：numnode() 为 0 时返回最近发布
SELECT id, title
FROM articles, websearch_to_tsquery('english', $1) q
WHERE numnode(q) = 0
   OR fts @@ q
ORDER BY
    CASE WHEN numnode(q) = 0 THEN 0 ELSE 1 END DESC,
    ts_rank(fts, q) DESC,
    created_at DESC
LIMIT 20;

PHP 端只需要传一个参数，不需要对输入做任何转义：

php
<?php
// 用户输入原样传入；websearch_to_tsquery 永不抛语法错误
$sql = <<<SQL
SELECT id, title
FROM articles, websearch_to_tsquery('english', :q) q
WHERE numnode(q) = 0 OR fts @@ q
ORDER BY
    CASE WHEN numnode(q) = 0 THEN 0 ELSE 1 END DESC,
    ts_rank(fts, q) DESC,
    created_at DESC
LIMIT 20
SQL;

$stmt = $pdo->prepare($sql);
$stmt->execute([':q' => $keyword]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 命中片段高亮必须回原表取原文（tsvector 里没有原文）

模拟输出：

text
 id |                title
----+--------------------------------------
  7 | PostgreSQL Full Text Search Tuning
  1 | PostgreSQL Full Text Search
 12 | GIN Index Internals
(3 rows)

注意事项 ​

不要对用户输入直接用 to_tsquery。 一个 &、 )、: 都可能让整条 SQL 抛异常，属于可用性问题甚至轻微的信息泄露面：

sql
-- 错误！用户输入 'c++ & (python' 直接导致异常
-- SELECT to_tsquery('english', 'c++ & (python');
-- ERROR:  syntax error in tsquery: "c++ & (python"

-- 推荐：websearch_to_tsquery 永不报错
SELECT websearch_to_tsquery('english', 'c++ & (python') AS safe_q;

表达式索引里禁止使用单参数 to_tsvector。 它不是 IMMUTABLE，建索引会直接失败：

sql
-- 错误！
-- CREATE INDEX idx ON articles USING gin (to_tsvector(body));
-- ERROR:  functions in index expression must be marked IMMUTABLE

查询表达式必须与索引表达式逐字一致，否则索引不会被使用。 大小写、schema 限定、字段拼接方式都必须相同：

sql
-- 索引建在 ('english', title || ' ' || body) 上
-- 下面这条能走索引（表达式一致）
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', $1)

-- 下面这条不能走索引（表达式不同）
WHERE to_tsvector('english', body) @@ to_tsquery('english', $1)

不要指望 plainto_tsquery 表达「或」与「非」。 它只生成 &，输入里的 or、- 都是普通词：

sql
-- 不推荐：用户想表达「猫或狗」，结果变成了「猫和狗」都必须有
SELECT plainto_tsquery('english', 'cat or dog') AS q;
text
        q
----------------
 'cat' & 'or' & 'dog'
(1 row)

注意 or 也变成了一个必须匹配的词位——这几乎一定不是用户想要的。

phraseto_tsquery 的 <N> 会让短语比想象中宽松。 停用词占位后，词间距可能变大——phraseto_tsquery('english', 'The Cat and Rats') 得到的是 'cat' <2> 'rat' 而不是 'cat' <-> 'rat'。如果业务要求严格紧邻，需要自己在应用层去掉停用词后再拼 <->。

array_to_tsvector 不做归一化。 传入的字符串会被原样当作词位，若数组里是大写或未词干化的词，与 to_tsvector 产出的向量无法匹配：

sql
-- 不推荐：'Rats' 未归一化，与 to_tsvector('english', ...) 的 'rat' 不相等
SELECT array_to_tsvector('{Rats}'::text[]) = to_tsvector('english', 'Rats') AS same;
text
 same
------
 f
(1 row)

strip() 之后再 setweight() 是无效操作。 权重依附于位置，位置没了权重无处可挂：setweight(strip('fat:2,4'::tsvector), 'A') 仍然返回 'fat'，看不到任何效果。

不要依赖 jsonb 转换出的位置信息。 jsonb 的键顺序是实现相关的，同一份文档每次可能得到不同的位置编号，因此不要对 jsonb_to_tsvector 的结果做短语匹配（@@ to_tsquery('english', 'a <-> b')）。

ts_delete 与 ts_filter 都返回新值，不修改原列。 需要写回时必须显式 UPDATE：UPDATE articles SET fts = ts_delete(fts, 'obsoleteterm') WHERE id = 1;。

ts_rewrite 的规则表顺序会影响结果。 多条规则可能同时命中，官方提示实际使用时应当对源查询 ORDER BY 某个排序键来固定应用顺序。

ts_stat 的入参是 SQL 文本而不是表达式。 引号嵌套容易出错，建议用美元引号包裹：

sql
-- 推荐：用 $$ 包裹，避免单引号转义地狱
SELECT * FROM ts_stat($$SELECT fts FROM articles WHERE lang = 'en'$$)
ORDER BY nentry DESC LIMIT 10;

最佳实践 ​

文档侧固定只用一个双参数 to_tsvector 表达式，并把它固化到生成列里。 这样查询端不需要重复写配置名，也不可能写错：

sql
ALTER TABLE articles
  ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;

查询侧默认使用 websearch_to_tsquery。 它是唯一「永不报错 + 语义符合直觉」的函数，配合前端搜索框的引号/减号语法体验最好：

用户输入websearch_to_tsquery('english', ...)
postgres index'postgres' & 'index'
"full text search"'full' <-> 'text' <-> 'search'
postgres or mysql'postgres' | 'mysql'
postgres -mysql'postgres' & !'mysql'

只有需要权重或前缀匹配时才退回 to_tsquery，且必须自己清洗输入。 这两个能力是 websearch_to_tsquery 不具备的：

sql
-- 推荐：前缀搜索前，先把用户输入里的 tsquery 元字符剥掉
SELECT to_tsquery('english', regexp_replace($1, '[&|!()<>:*'']', '', 'g') || ':*');

配置名一律写 schema 限定形式：内置配置写 pg_catalog.english（如 to_tsvector('pg_catalog.english', body)），自定义配置写 public.xxx。理由与官方在触发器文档中的说明一致：避免 search_path 变化导致行为漂移。

空查询必须短路。 numnode(q) = 0 时不要直接 @@，否则用户会看到「搜索什么都是空」，应当写成 WHERE numnode(q) = 0 OR fts @@ q。

字段权重按「标题 A、关键词 B、摘要 C、正文 D」分层。 这是官方推荐的默认分层，与 ts_rank 的默认权重数组 {0.1, 0.2, 0.4, 1.0} 正好对应：

sql
setweight(to_tsvector('pg_catalog.english', coalesce(title,'')),   'A') ||
setweight(to_tsvector('pg_catalog.english', coalesce(keyword,'')), 'B') ||
setweight(to_tsvector('pg_catalog.english', coalesce(abstract,'')),'C') ||
setweight(to_tsvector('pg_catalog.english', coalesce(body,'')),    'D')

NULL 字段必须 coalesce 兜底。 to_tsvector(NULL) 返回 NULL，而 NULL || anything 仍然是 NULL，会让整列向量变成 NULL。所以拼接前一律写成 to_tsvector('pg_catalog.english', coalesce(title, '') || ' ' || coalesce(body, ''))。

用 IMMUTABLE 的双参数版本写索引与查询，并保持表达式逐字一致：

sql
CREATE INDEX idx_articles_fts ON articles
USING gin (to_tsvector('pg_catalog.english', coalesce(title,'') || ' ' || coalesce(body,'')));

上线前用 ts_debug 抽查一批真实数据。 至少覆盖中文混排、带连字符的词、邮箱与 URL、全大写、超长单词：

sql
SELECT token, dictionary, lexemes
FROM ts_debug('pg_catalog.english', 'PostgreSQL-18 e-mail: a@b.com 全大写TEST')
WHERE lexemes IS NULL OR lexemes = '{}';

用 ts_stat 定期检查停用词与分词质量。 ndoc 高的词考虑加进停用词表；被异常切碎的词位说明配置需要调整：

sql
SELECT word, ndoc, nentry
FROM ts_stat($$SELECT fts FROM articles$$)
ORDER BY ndoc DESC, nentry DESC
LIMIT 20;

读取向量内容一律用 tsvector_to_array() 或 unnest()，不要正则解析 ::text。 文本表示虽然稳定，但它是给人和调试用的，不是契约。

不要用 plainto_tsquery 替换 websearch_to_tsquery 来「更安全」。 两者都不会报错，但前者会丢失用户的 or 与 - 语义，体验更差。

下一节

继续学习：搜索排名与权重（ts_rank）

参考链接 ​