数组列的Postgres全文搜索 [英] Postgres Full Text Search on Array Column

问题描述

我有一个帖子，其中有一个列 tags 。我希望能够对所有标签进行全文搜索。对于 VARCHAR 列，我使用过：

I have a posts that has a column tags. I'd like to be able to do full text search across the tags. For VARCHAR columns I've used:

CREATE INDEX posts_fts_idx ON posts USING gin(to_tsvector('english', coalesce(title, ''));
SELECT "posts".* FROM "posts" WHERE (to_tsvector('english', coalesce(title, '')) @@ (to_tsquery('english', 'ruby')));

但是，对于字符变化[] 函数 to_tsvector 不存在。如何编写针对每个标签运行的查询（理想情况下是匹配的）

However, for character varying[] the function to_tsvector does not exist. How can a query be written that will run against each of the tags (ideally matching if any single tag matches)?

注意：我发现将字符串转换为字符串非常容易（ array_to_string ），但如果可能的话，我想将每个标签转换为 tsvector 。

Note: I see that it would be pretty easy to do a conversion to a string (array_to_string) but if possible I'd like to convert each individual tag to a tsvector.

推荐答案

您可以将使用gin作为搜索选项的字符编入索引。

You could index the character varying using gin for search options. Try this :

CREATE INDEX idx_post_tag ON posts USING GIN(tags);

SELECT * FROM posts WHERE  tags @> (ARRAY['search string'::character varying]);

这是需要精确匹配的时间。如果不需要完全匹配，则应考虑将标签存储为文本列。多想想这些标签的重要性。字符串数组类型缺少文本索引，词干和词尾变化支持，因此您将无法匹配舞蹈与舞蹈之类的东西。

This is when an exact match is desired. If an exact match is not desired, you should consider storing your tags as a text column. Think more on the significance of these 'tags'. String array types lack text indexing, stemming and inflection support, and hence you won't be able to match bates such as 'Dancing' with 'Dance'.

不是一种选择，您可以使用不可变版本的array_to_string函数来规避此问题。您的查询将是：

If that is not an option, you could circumvent this with an immutable version of array_to_string function. Your queries would then be :

CREATE INDEX posts_fts_idx ON posts USING gin(to_tsvector('english', immutable_array_to_string(tags, ' ')));
SELECT "posts".* FROM "posts" WHERE (to_tsvector('english', immutable_array_to_string(tags, ' ')) @@ (to_tsquery('english', 'ruby')));

这篇关于数组列的Postgres全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！