PostgreSQL全文搜索相似的单词 [英] Postgresql full text search for similar words

问题描述

按照以下步骤尝试实现postgresql全文搜索示例；

Trying to implement postgresql full text search example by following these steps;

我创建了一个表：

CREATE TABLE posts
(
id serial primary key,
content varchar(255),
tags varchar(255),
title varchar(255)
);

更改表以使其具有TSVECTOR类型的列

Altered the table to have a column of type TSVECTOR

ALTER TABLE posts ADD COLUMN searchtext TSVECTOR;

创建触发器以将数据存储在searchtext列中：

Created a trigger to store data in searchtext column:

CREATE TRIGGER ts_searchtext BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('searchtext', 'english', 'title', 'content', 'tags')

我已插入一行：

insert into posts(content,tags,title) values('once upon a time I had a fat friend','friend, fat','Fat Boy');

在搜索文本列下的值是：

Value under searchtext column is:

"'boy':2 'fat':1,10,13 'friend':11,12 'time':6 'upon':4"

现在，当我运行查询时：

Now, when I run query:

SELECT * FROM posts where searchtext @@ to_tsquery('english','fot');

返回0行。

因此，基本上，我正在寻找一种解决方案，将 fot与 fat进行匹配，或者我们可以说基于单词中匹配的字母来寻找结果。

So, basically I am looking for a solution where "fot" should have been matched against "fat" or we can say looking for result based on matched letters out of word.

或者，如果我们运行：

SELECT * FROM posts where searchtext @@ to_tsquery('english','frend');

应与 friend匹配。根据postgresql的文档，我认为它将在词典的帮助下完成。但是现在确定我应该使用哪个.dict和.affix文件。谁能帮助我进一步了解这一点。

It should match against "friend". As per documentation of postgresql I think it will be done with the help of dictionaries. But now sure which .dict and .affix file should I use. Can anyone help me with more understanding on this.

推荐答案

尝试使用plainto_tsquery函数：

Try with plainto_tsquery function:

SELECT * FROM帖子搜索文本@@ plainto_tsquery（'english frend'）;

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