From: | Anders Østergaard Jensen <aj(at)itersys(dot)dk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | tsearch2 query question |
Date: | 2010-08-08 02:59:59 |
Message-ID: | AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-08-08 03:35:24 | Re: Using SKYLINE command on PostgreSQL |
Previous Message | Tom Lane | 2010-08-07 20:22:30 | Re: pl/python out params |