Make postgres full text search (tsvector) act like ILIKE to search inside words?

Question

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.

Answer 1

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:

• PostgreSQL LIKE query performance variations
• Pattern matching with LIKE, SIMILAR TO or regular expressions