将以前缀结尾的短语与全文搜索匹配

本文介绍了将以前缀结尾的短语与全文搜索匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在寻找一种方法来模拟 SELECT * FROM table WHERE attr LIKE'％text％'在PostgreSQL中使用tsvector。

I'm looking for a way to emulate something like SELECT * FROM table WHERE attr LIKE '%text%' using a tsvector in PostgreSQL.

我没有使用字典就创建了tsvector属性。现在，这样的查询...

I've created a tsvector attribute without using a dictionary. Now, a query like ...

SELECT title FROM table WHERE title_tsv @@ plainto_tsquery('ph:*');

...将返回所有标题，例如 Physics， PHP等。但是如何我创建了一个查询，该查询返回标题以 Zend Fram开头的所有记录（应该返回 Zend Framework的实例）？

... would return all titles like 'Physics', 'PHP', etc. But how can I create a query that returns all records where the title start with 'Zend Fram' (which should return for instance 'Zend Framework')?

当然，我可以使用

SELECT title FROM table WHERE title_tsv @@ to_tsquery('zend') AND title_tsv @@ to_tsquery('fram:*');

但是，这似乎有点尴尬。

However, this seems a little awkward.

所以，问题是：有没有办法使用上述类似的公式来表达上面给出的查询：

So, the question is: is there a way to formulate the query given above using something like:

SELECT title FROM table WHERE title_tsv @@ to_tsquery('zend fram:*');

推荐答案

SELECT title FROM table WHERE title_tsv @@ to_tsquery('zend') and title_tsv @@ to_tsquery('fram:*')

等同于：

SELECT title FROM table WHERE title_tsv @@ to_tsquery('zend & fram:*')

当然也可以找到 Zend没有框架。

but of course that finds "Zend has no framework" as well.

您当然可以在tsquery匹配后针对标题表达正则表达式匹配，但是您必须使用解释分析以确保该查询是在tsquery之后而不是之前执行的。

You could of course express a regular expression match against title after the tsquery match, but you would have to use explain analyze to make sure that was being executed after the tsquery instead of before.