From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anders Østergaard Jensen <aj@itersys.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: tsearch2 query question
Date: Sun, 08 Aug 2010 11:51:12 -0400
Message-ID: <3728.1281282672@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com>
References: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com>
=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, aj@itersys.dk
Subject: Re: tsearch2 query question
In-Reply-To: <3728.1281282672@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox