Postgres：如何在现有的varchar列上添加全文搜索？

Question

Postgres：如何在现有的varchar列上添加全文搜索？

5

我有一个现有的Postgres 9.3数据库，其中有一个包含varchar列的表。

        Table "public.frontend_chemical"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 bnf_code  | character varying(9)   | not null
 chem_name | character varying(200) | not null

我希望在chem_name列上运行全文搜索。

我阅读了这篇文章，其中建议的步骤如下：

1. 添加一个新的tsvector列：ALTER TABLE frontend_chemical ADD COLUMN fts_document tsvector; 2. 创建一个将chem_name列映射到文档的函数和一个触发器以保持其更新。 3. 在该列上创建GIN索引：CREATE INDEX chem_fts_index ON frontend_chemical USING gin(fts_document)；

然后我应该能够运行全文搜索查询，如：SELECT COUNT(*) FROM frontend_chemical WHERE fts_document @@ 'statin';。

首先，这一般的过程正确吗？

其次，如何将所有现有的chem_name列中的条目映射到fts_document列？文章中的示例似乎只在更新chem_name列时更新document列，而我有一个大型现有数据库。

- Richard

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vérité · Accepted Answer

这个过程是正确的，但在你的情况下可能有些繁琐。

由于只需要对单列进行全文搜索，因此可以省略专用的tsvector列，只创建如下的GIN索引：

CREATE INDEX chem_fts_index ON frontend_chemical
    USING gin(to_tsvector('simple',chem_name));

除了使用simple，如需使用语言规则，您可以指定english或其他可用配置。

这样，在搜索时就能从索引中受益：

select columns from frontend_chemical where
   to_tsvector('simple', chem_name) @@ to_tsquery('simple','expression to search');

关键点在于tsvector表达式与GIN索引中完全相同。

这样做的好处是不需要触发器，可以节省专用列的空间，因为该列的值已经在索引中了，并且不需要初始化该列（您的第二个问题）。

如果您仍然需要该列，则应使用以下形式的更新查询进行初始填充：

UPDATE frontend_chemical SET fts_document = to_tsvector('simple', chem_name);

(再次假设simple为文本搜索配置)

跟随评论进行编辑:

to_tsquery()只使用一个参数时会使用默认的文本配置(否则应该将配置名称作为第一个参数传递)。如果此默认值与to_tsvector中使用的不匹配，则会出现问题。可以通过多种方式更改默认值:

在会话期间(非持久性)

    SET default_text_search_config to 'simple';

用于数据库（持久性）

    ALTER DATABASE nameofdb SET default_text_search_config to 'simple';

否则，始终使用两个参数的形式来调用to_tsquery函数，并将明确的文本配置名称作为第一个参数（我已更改上面的示例以使用该形式）。

如果您想要搜索前缀为Ro的内容，可以使用以下条件：

to_tsvector('simple', chem_name) @@ to_tsquery('simple', 'Ro:*')

请查看手册中的控制文本搜索部分了解更多信息。