Postgresql全文搜索 [英] Postgresql full text search

问题描述

我们在Postgresql数据库中有大约2千万条记录。其中一列是html文档，存储为Text数据类型。我们希望在搜索功能中使用此列。我们已将html内容转换为纯文本并使用to_tsvector创建标记。我们已在此令牌列上实施全文搜索。



1.当我们使用一个单词和逻辑运算符进行搜索时，它会按预期返回结果。示例：

We have around 20 millions of records in Postgresql database. One of the column is html document stored as Text datatype. We want Use this column in search functionality. We have converted html content to plaintext and created tokens using to_tsvector. We have implemented full text search on this token column.

1. When we search with one word and logical operators, it returns results as expected. Example:

("Jack" AND ("Jill" OR "Tom") )

2.但是，当我们使用两个或更多时用逻辑运算符编写搜索字符串，然后它没有给出预期的输出。示例：对于搜索字符串

2. However, when we use two or more worded search strings with logical operators, then it is not giving expected output. Example: for search string

("Jack Jill" OR "Jack Tom")

，它不会一起搜索Jack Jill，但如果它找到了这两个单词它返回一个文本。在这里，如果专栏有杰克瑞恩马克吉尔，它将返回该行，但我们期待它只返回它像杰克吉尔瑞恩马克。



你能帮我们解决这个问题。如果您需要我们方面的任何细节，请告诉我们。



我尝试过的事情：

, it is not searching for "Jack Jill" together but if it finds those two words in one text it returns. Here, if the column has "Jack Ryan Mark Jill", it will return the row but, we are expecting it to return only when it is like "Jack Jill Ryan Mark".

Can you please help us solve this issue. Let us know if you need any details from our side.

What I have tried:

SELECT * from tablename where html_tokens(column where I am storing the token)  @@ to_tsquery((ikeyword) 

推荐答案

您需要使用邻近运算符< - >

所以这将是：

You need to use the proximity operator <->
So that would be:

"Jack <-> Jill"

请参阅此处的示例：掌握PostgreSQL工具：全文搜索和短语搜索 - 撰写文章 [ ^ ]

See examples here: Mastering PostgreSQL Tools: Full-Text Search and Phrase Search - Compose Articles[^]

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