将以前缀结尾的短语与全文搜索匹配 [英] Match a phrase ending in a prefix with full text search

问题描述

我正在寻找一种方法来模拟 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.

这篇关于将以前缀结尾的短语与全文搜索匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！