Postgresql前缀通配符用于全文

问题描述

我尝试使用Postgresql运行全文查询，可以使用通配符进行局部匹配。

在搜索词后面有一个后缀通配符似乎很容易，但我无法弄清楚如何指定前缀通配符。

例如，我可以使用类似的方式轻松地执行后缀搜索。

  SELECTt1。* 
从t1
 WHERE（to_tsvector（'simple'，t1。city ）@@ to_tsquery（'simple'，'don：*'））
  

应返回结果匹配伦敦

然而，我似乎无法像前缀搜索...

  SELECTt1。* 
从t1
 WHERE（to_tsvector（'simple'，t1。city）@@ to_tsquery（'simple'，'：*： ））
  

理想情况下，我想在前面和末尾添加一个通配符搜索字词，类似...

  SELECTt1。* 
 FROMt1 
 WHERE（to_tsvector（'simple'，t1。city）@@ to_tsquery（'simple'，'：* don：*'））
  

我可以使用LIKE条件，但是我希望能够从Postgres中的全文搜索功能的性能中受益。

解决方案

全文搜索很适合查找单词，而不是子字符串。

对于子字符串搜索，最好使用，比如'％don％'和 pg_trgm使用gin（column_name gin_trgm_ops）或使用gist（column_name gist_trgm_ops）可以从PostgreSQL 9.1和扩展 c>索引。但是你的索引会非常大（甚至比你的表大几倍），并且写出的表现不是很好。



有一个。

I am trying to run a fulltext query using Postgresql that can cater for partial matches using wildcards.

It seems easy enough to have a postfix wildcard after the search term, however I cannot figure out how to specify a prefix wildcard.

For example, I can perform a postfix search easily enough using something like..

SELECT "t1".* 
FROM "t1" 
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', 'don:*') )

should return results matching "London"

However I cant seem to do a prefix search like...

SELECT "t1".* 
FROM "t1" 
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', ':*don') )

Ideally I'd like to have a wildcard prefixed to the front and end of the search term, something like...

SELECT "t1".* 
FROM "t1" 
WHERE (to_tsvector('simple', "t1"."city") @@ to_tsquery('simple', ':*don:*') )

I can use a LIKE condition however I was hoping to benefit from the performance of the full text search features in Postgres.

解决方案

Full text search is good for finding words, not substrings.

For substring searches you'd better use like '%don%' with pg_trgm extension available from PostgreSQL 9.1 and using gin (column_name gin_trgm_ops) or using gist (column_name gist_trgm_ops) indexes. But your index would be very big (even several times bigger than your table) and write performance not very good.

There's a very good example of using pg_trgm for substring search on select * from depesz blog.

这篇关于Postgresql前缀通配符用于全文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！