PG full text search on rails using pg_search gem f

Question

I am using Pg full text search for my search . As i am using Ruby on rails, I am using pg_search gem. How do i configure it to give a hit for substring as well.

pg_search_scope :search_by_detail, 
              :against => [
                   [:first_name,'A'],
                   [:last_name,'B'],
                   [:email,'C']
              ],                  
              :using => {
                :tsearch => {:prefix => true}
              }

Right now it gives a hit if the substring is in the start but it wont give a hit if the substring in the middle

example It gives a hit for [e-mail] but not for example.com

Answer 1

I'm the author and maintainer of pg_search.

Unfortunately, PostgreSQL's tsearch by default doesn't split up email addresses and allow you to match against parts. It might work if you turned on :trigram search, though, since it matches arbitrary sub-strings that appear anywhere in the searchable text.

pg_search_scope :search_by_detail,
                :against => [
                  [:first_name,'A'],
                  [:last_name,'B'],
                  [:email,'C']
                ],
                :using => {
                  :tsearch => {:prefix => true},
                  :trigram => {}
                }

I confirmed this by running the following command in psql:

grant=# SELECT plainto_tsquery('example.com') @@ to_tsvector('english', '[e-mail]');
 ?column? 
----------
 f
(1 row)

I know that the parser does detect email addresses, so I think it must be possible. But it would involve building a text search dictionary in PostgreSQL that would properly split the email address up into tokens.

Here is evidence that the text search parser knows that it is an email address:

grant=# SELECT ts_debug('english', '[e-mail]');
                                  ts_debug                                   
-----------------------------------------------------------------------------
 (email,"Email address",[e-mail],{simple},simple,{[e-mail]})
(1 row)