你如何在利用全文索引的postgres中进行基于短语的全文搜索？ [英] How do you do phrase-based full text search in postgres that takes advantage of the full-text index?

问题描述

$ b

CREATE TABLE t1（body text，body_vector tsvector）; $ b

我希望能够使用全文索引（tsvector列中的GiST，GiN或两者）来搜索短语。我已经能够找到的最佳解决方法是首先对两个单词（布尔AND）进行全文搜索，然后对该短语的主体进行类似的比较。当然，这不会捕获postgres的全文搜索为您做的任何词干或拼写检查。一个例子是，如果我正在搜索短语'w1 w2'，我会用：

SELECT * FROM t1 WHERE body_vector @@'w1& w2':: tsquery AND body LIKE'w1 w2';

有没有办法做到这一点，你不必诉诸在文本列上搜索？

解决方案

如果您需要精确的词组匹配，那就是实现它的方法。您也可以尝试WHERE body_vector @@ plainto_tsquery（'w1 w2'），然后通过排序进行排序。 （重点在于单词相邻的命中应该在最上面）

Let's say you have a postgres 8.3 table as follows:

CREATE TABLE t1 (body text, body_vector tsvector);

I want to be able to search it for phrases using the full text index (GiST, GiN or both on the tsvector column). The best workaround I've been able to find is to first do the full text search on both words (boolean AND) and then do a like comparison on the body for the phrase. Of course, this fails to capture any stemming or spell-checking that postgres' full-text search does for you. An example of this is if I'm searching for the phrase 'w1 w2', I'd use:

SELECT * FROM t1 WHERE body_vector @@ 'w1 & w2'::tsquery AND body LIKE 'w1 w2';

Is there a way to do this where you don't have to resort to searching on the text column?

解决方案

If you want exact phrase matching, that's the way to do it. You can also try WHERE body_vector @@ plainto_tsquery('w1 w2'), and then order it by ranking. (the point being that the hits where the words are right next to each other should end up on top)

这篇关于你如何在利用全文索引的postgres中进行基于短语的全文搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！