6

So let's say I search for 'Blerg'. And I have a item with the name SomethingblergSomething.

If I do an ILIKE search in postgres (and rails) like this:

where("name ILIKE ?", "%#{ 'Blerg' }%")

It will return the result 'SomethingBlergSomething' because it contains Blerg.

Is there a way to make the faster tsvector do a similar style of searching inside a word:

where("(to_tsvector('english', name) @@ to_tsquery(?))", ('Blerg' + ':*'))

The above query will not return 'SomethingBlergSomething'.

So how do I make tsvector act like ILIKE when searching inside words.

CafeHey
  • 5,699
  • 19
  • 82
  • 145

1 Answers1

7

Are you aware of trigram search, provided by the additional module pg_trgm? That seems more appropriate for your use case than text search.

With a trigram index in place (GIN or GiST) you can use your original ILIKE predicate and get index support for it. You need Postgres 9.1+ for that.

Details:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228