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
