PostgreSQL全文搜索是否支持类LIKE/ILIKE的通配匹配？

PostgreSQL全文搜索可以实现类似ILIKE的前缀匹配，但原生不直接支持后缀匹配，且逻辑和LIKE存在差异（全文搜索基于分词匹配，LIKE是原始字符串的模式匹配）。以下针对你的问题逐一说明：

一、你的当前查询方式是否可行？ #

你使用的WHERE u.fts_search_ts && to_tsquery('KE:*')写法存在两处问题：

• 你用english词典创建tsvector，该词典会对文本做词干化处理（比如"Katherine"会被处理为"katherin"），而KE:*是针对分词后的词前缀匹配，若原始文本的词干不以"ke"开头，就无法匹配到结果。
• 全文搜索的匹配判断应该用@@操作符，&&仅用于判断两个tsvector是否存在交集，不符合你的查询需求。

二、实现前缀匹配（类似ILIKE 'KE%'） #

方案1：使用simple词典避免词干化 #

如果需要严格匹配原始文本的词前缀（不做词干提取），可以将tsvector的词典改为simple（仅做小写转换，不处理词干）：

1. 修改索引列：

ALTER TABLE Users
ADD COLUMN fts_search_ts tsvector
    GENERATED ALWAYS AS (to_tsvector('simple',  
        coalesce(firstname, '') || ' ' || 
        coalesce(account_number, '') )
    ) STORED;

-- 创建GIN索引提升查询效率
CREATE INDEX idx_users_fts_search ON Users USING GIN(fts_search_ts);

2. 前缀匹配查询：

SELECT * FROM Users 
WHERE fts_search_ts @@ to_tsquery('simple', 'KE:*');

该查询会匹配所有分词后以"ke"开头的词（对应原始文本中以"KE"开头的词，因为simple词典会将"KE"转为小写"ke"），效果接近ILIKE 'KE%'（但针对词级别的前缀，而非整个字段的前缀）。

方案2：基于english词典的词干前缀匹配 #

如果必须保留english词典的词干化能力，需要确保查询词的词干与分词后的词干匹配：

SELECT * FROM Users 
WHERE fts_search_ts @@ to_tsquery('english', 'ke:*');

此查询会匹配所有词干以"ke"开头的分词，适合需要词干化的场景（比如匹配"Katherine"、"Kevin"等词干相近的词汇）。

三、实现后缀匹配（类似ILIKE '%EN'） #

PostgreSQL全文搜索原生不支持后缀匹配（倒排索引结构对后缀查询优化有限），可以通过以下两种方式实现：

方案1：反转字符串模拟后缀匹配 #

通过反转文本并创建反转后的tsvector，将后缀匹配转为前缀匹配：

1. 添加反转文本的tsvector列：

ALTER TABLE Users
ADD COLUMN fts_search_rev_ts tsvector
    GENERATED ALWAYS AS (to_tsvector('simple',  
        reverse(coalesce(firstname, '')) || ' ' || 
        reverse(coalesce(account_number, '')) )
    ) STORED;

CREATE INDEX idx_users_fts_rev_search ON Users USING GIN(fts_search_rev_ts);

2. 后缀匹配查询（比如匹配以"EN"结尾的词）：

SELECT * FROM Users 
WHERE fts_search_rev_ts @@ to_tsquery('simple', reverse('EN') || ':*');

反转搜索词"EN"得到"NE"，通过前缀匹配反转后的分词，等价于匹配原始词以"EN"结尾。

方案2：直接使用LIKE/ILIKE加专用索引 #

如果数据量不大或需要整个字段的后缀匹配，直接用ILIKE并创建varchar_pattern_ops类型的B-tree索引更高效：

-- 创建支持LIKE模式匹配的索引
CREATE INDEX idx_users_firstname_like ON Users USING btree (firstname varchar_pattern_ops);

-- 后缀匹配查询
SELECT * FROM Users WHERE firstname ILIKE '%EN';

内容的提问来源于stack exchange，提问作者funcfunc