Partial matches with postgresql full-text search using text on Heroku

Question

Attempting a job search on Heroku using partial search

The following query generates an SQL error on Heroku, but it works correctly in my local version:

@events.search(params[:search]+":*") 

I use the Heroku shared database service, is this a possible syntax difference between versions of PostgreSQL?

What syntax should I use to do a partial search for full-text index matches in PostgreSQL 8?

Answer 1

It turns out PostgreSQL version 8 does not support partial searches using the syntax: *.

Answer 2

Listed below are the changes in PostgreSQL 9.1 .

Perhaps you can try using string interpolation instead of concatenation.

 @events.search("#{params[:search]}:*") 

I'm not sure the emotion of a kiss: * adds to the texticle . Maybe I need to learn more SQL.

Answer 3

As http://www.postgresql.org/docs/9.0/interactive/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES says that using ":" is to indicate prefix matching. For instance. If the search "Australia" with "Aus:" will work, but not "ust: *".

So concat OR xxxx LIKE "% yyy%" will work better

Answer 4

You can use the tsearch option with a prefix:

 :tsearch => {:prefix => true}