To: =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <aj@itersys.dk>
cc: pgsql-sql@postgresql.org
Subject: Re: tsearch2 query question 
In-reply-to: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com> 
References: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com>
Comments: In-reply-to =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?=
	<aj@itersys.dk> message dated "Sun, 08 Aug 2010 12:59:59 +1000"
Date: Sun, 08 Aug 2010 11:51:12 -0400
Message-ID: <3728.1281282672@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <aj@itersys.dk> writes:
> Now, let's start out with a classic, non-tsearch2 ILIKE query with two
> wildcards around the search token:
> metabase=# select id, name from customers where name ilike '%holstebr%';
>   id  |       name
> ------+-------------------
>  3646 | Holstebro Kommune
> (1 row)

> I am trying to do the same exactly with tsearch2 but with no luck:

> metabase=# select id, name from customers where search_idx @@
> to_tsquery('*Holstebr*');

There isn't any exact equivalent for that in tsquery, because it's about
searching for words not arbitrary substrings.  You could do
to_tsquery('Holstebro').  Since 8.4 you could also do a prefix match,
say to_tsquery('Holstebr:*'), but there's no way to omit letters from
the start of the word.  Also, you have to be wary of prefix matches
if you use a stemming dictionary, because dropping characters from the
end of the word might make it stem differently.

> And subsequently: how do I handle spaces in between, fx. so that
> 'holstebro komm*' yields 'holstebro kommune'?

You need to think of that as being two independent word searches,
like to_tsquery('holstebro & komm:*').  AFAIR there isn't any phrase
matching per se in tsquery.  Of course, you could AND this with an ILIKE
clause to filter out matches where the words weren't adjacent.

If you're really dependent on the exact behavior of LIKE-style
searching then tsquery is not going to provide you with an exact
replacement.  You might consider looking at contrib/pg_trgm/ to
find an indexable operation that can speed up LIKE searches.

			regards, tom lane