PostgreSQL对数组列的全文搜索

Question

我有一张名为 posts 的表，其中有一个名为 tags 的列。我希望能够在标签中进行全文搜索。对于 VARCHAR 类型的列，我已经使用了:

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')));

然而，对于"character varying[]"，函数"to_tsvector"不存在。可以如何编写查询以针对每个标签运行（如果任何一个标签匹配，则最理想的是匹配）？

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

- Kevin Sylvestre

Answer 1

您可以使用gin对character varying进行索引以进行搜索。尝试以下操作：

CREATE INDEX idx_post_tag ON posts USING GIN(tags);

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

这是在需要精确匹配时的做法。如果不需要精确匹配，您应该考虑将标签存储为文本列。请思考这些“标签”的意义。字符串数组类型缺乏文本索引、词干提取和屈折变化支持，因此您将无法将'bates'（如'Dancing'）与'Dance'等匹配。

如果那不是一个选项，你可以通过不可变版本的array_to_string函数绕过这个问题。然后你的查询将是：

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')));

- TavoloPerUno

---

是的，我在我的问题中已经提到了这一点 - 我想尝试在数组的每个元素上应用tsvector而不是连接数组然后应用。问题在于这将匹配标签之间的字符串。 - Kevin Sylvestre

为了使其正常工作，您必须将标签移动到单独的表中。我没有看到其他可能性。 - TavoloPerUno

这个 immutable_array_to_string 函数来自哪里？ - Jonathan Allard

我找不到immutable_array_to_string的任何文档。 - Carlos Martinez

创建或替换函数 immutable_array_to_string(text[]) 返回文本，如下所示： $$ SELECT array_to_string($1, ','); $$ 语言 SQL IMMUTABLE; - HelloWorld