PostgreSQL中如何对含多值的列实现to_tsquery前缀匹配？

云原生阿岚

2026-7-11

解决方案

要实现从包含空格的列生成带前缀匹配（:*）的tsquery，可以通过手动构造符合格式的查询字符串再转换为tsquery的方式解决，具体步骤如下：

1. 直接构造查询逻辑

通过字符串拆分、拼接生成带:*的查询字符串，再传入to_tsquery：

select 
  to_tsvector('english', 'matchsticks only applicable things'),
  -- 构造带前缀匹配的tsquery字符串并转换
  to_tsquery('english', array_to_string(regexp_split_to_array(trim(db_column), '\s+'), ':*&') || ':*'),
  -- 执行匹配
  to_tsvector('english', 'matchsticks only applicable things') @@ to_tsquery('english', array_to_string(regexp_split_to_array(trim(db_column), '\s+'), ':*&') || ':*')
from your_table;

trim(db_column)：去除字符串首尾空格
regexp_split_to_array(..., '\s+')：按任意数量空格拆分单词，避免连续空格导致的空元素
array_to_string(..., ':*&')：用:*&连接拆分后的单词，再追加:*，最终生成类似match:*&test:*&square:*的字符串

2. 封装为自定义函数（推荐）

如果需要多次复用，可创建自定义函数简化调用：

create or replace function text_to_prefix_tsquery(text, regconfig default 'english')
returns tsquery as $$
begin
  -- 处理空值或空白字符串
  if $1 is null or trim($1) = '' then
    return ''::tsquery;
  end if;
  return to_tsquery($2, array_to_string(regexp_split_to_array(trim($1), '\s+'), ':*&') || ':*');
end;
$$ language plpgsql immutable;

使用示例：

select 
  to_tsvector('english', 'matchsticks only applicable things'),
  text_to_prefix_tsquery(db_column),
  to_tsvector('english', 'matchsticks only applicable things') @@ text_to_prefix_tsquery(db_column)
from your_table;