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

Question

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?

Of course there are differences between versions. You already suspect version differences. Would seem prudent to mention your version. Heroku shared should be 8.3. You may be interested: [Heroku offers PostgreSQL 9.1](https://postgres.heroku.com/blog/past/2012/1/18/postgresql_91_available_in_beta/) in beta by now. — Erwin Brandstetter, Feb 24 '12 at 05:50
Yes, but what are the differences? The docs don't suggest there is a different syntax between 8 and 9. — Toby Hede, Feb 24 '12 at 10:37
You can use `%` for partial matches. Have you tried it? i.e, `@events.search("#{params[:search]}:%")` — Harish Shetty, Mar 02 '12 at 00:42
The % syntax works with LIKE, but does not appear to be supported within PG's full-text search. — Toby Hede, Mar 04 '12 at 02:22

James Dunn · Answer 1 · 2012-02-27T02:31:15.470

3

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.

edited Feb 27 '12 at 02:31

answered Feb 27 '12 at 00:19

James Dunn

That is the same syntax that is failing on PostgreSQL Version 8. I have tried both concatenation and interpolation. The syntax is meant to be for partial matches, similar to an SQL "LIKE 'search%'". – Toby Hede Feb 28 '12 at 00:40
This is something that I'd like to do with texticle as well. I can't find anything in the texticle docs about partial searches. You can however accomplish this with the pg_search gem, using the :prefix => true option. That's probably not what you want to hear though. – James Dunn Feb 29 '12 at 19:16
1

I found a commit on github that hints at what you're trying to do. https://github.com/tenderlove/texticle/commit/aff41d690b5dcb595bfa0ed8f21509859c4e6519 – James Dunn Feb 29 '12 at 19:34

score 2 · Accepted Answer · answered Mar 04 '12 at 02:21

2

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

answered Mar 04 '12 at 02:21

Toby Hede

score 1 · Answer 3 · answered May 02 '12 at 07:15

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

score 0 · Answer 4 · answered Jan 11 '17 at 11:06

0

You can use the tsearch option with a prefix:

:tsearch => {:prefix => true}

answered Jan 11 '17 at 11:06

The Whiz of Oz

4 Answers4