=?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <a...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql