Menu
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.
387
Yes, You Can Keep Full-Text Search in Postgres You can get even deeper and make your Postgres full-text search even more robust, by implementing features such as highlighting results, or writing your own custom dictionaries or functions.
Text search by using to_tsvector function and operator We have search text by using the to_tsvector function in PostgreSQL. In to_tsvector, “ts” is defined as text search. In to_tsvector, the tsvector is the data type of to_tsvector function. This function will return the lexeme tokens with pointers in PostgreSQL.
Introduction. Full-text search (FTS) is a technique used by search engines to find results in a database. It can be used to power search results on websites like shops, search engines, newspapers, and more.
A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word (see Chapter 12 for details).
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:
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
