Hello postgresql users,
I have a question regarding building a tsearch2 query that does wildcard searching by default. Currently, I am using plainto_tsquery(<expr>), but it does not allow me to build more advanced search expressions. I know to_tsquery is the way to go with this, but I need some qualified assistance for building the correct query.
Consider the following table:
metabase=# \d customers;
Table "public.customers"
Column | Type | Modifiers
-----------------+--------------------------+-----------------------------------------------------------------
id | integer | not null default nextval(('"customer_id_seq"'::text)::regclass)
name | character varying(255) | not null
description | text |
[...]
search_idx | tsvector |
search_idx is a tsvector with an ON INSERT OR UPDATE trigger, which automatically updates the search vector as a combination of name and description. No black magic there.
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%';
CASE #1:
id | name
------+-------------------
3646 | Holstebro Kommune
(1 row)
I am trying to do the same exactly with tsearch2 but with no luck:
CASE #2:
metabase=# select id, name from customers where search_idx @@ to_tsquery('*Holstebr*');
id | name
----+------
(0 rows)
How do I formulate the input for to_tsquery so that it returns the same as (1)? And subsequently: how do I handle spaces in between, fx. so that 'holstebro komm*' yields 'holstebro kommune'?
If people have any example of expression builders they put on top of their SQL queries, I would love to see it. I know that this has been done before.
Thank you for your time and help,
Anders
