CODEWITHSUNDEEP
ruby-on-railspostgresqlherokutexticle
Toby Hede
Toby Hede

Reputation: 37133

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

Trying to get searching working on Heroku using partial search

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

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

I am using the Heroku shared database service, is this a possible difference in syntax between PostgreSQL versions?

What syntax should I be using to do a partial matching searching against a full-text index in PostgreSQL 8?

Upvotes: 4

Views: 1891

Answers (4)

The Whiz of Oz
The Whiz of Oz

Reputation: 7043

You can use the tsearch option with a prefix:

:tsearch => {:prefix => true}

Upvotes: 0

Lecky Lao
Lecky Lao

Reputation: 369

As http://www.postgresql.org/docs/9.0/interactive/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES says, using ":" is for specify prefix matching. E.g. If searching "Australia" with "Aus:" will work but not "ust:*".

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

Upvotes: 1

Toby Hede
Toby Hede

Reputation: 37133

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

Upvotes: 2

James Dunn
James Dunn

Reputation: 192

Here are the changes in PostgreSQL 9.1.

Perhaps you could try using string interpolation instead of concatenation.

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

I'm not really sure what the kiss emoticon :* adds to texticle's functionality. Maybe I need to learn more SQL.

Upvotes: 3

Related Questions