Thread: How to perform full text search

How to perform full text search

From
"Andrus"
Date:
User can enter any number of words as search string.
In shopping cart the following query is used to find products,
eq. if "red cat" is entered:
 
select * from products
where  productname ilike '%'||'red cat'||'%'
  or productdescription ilike '%'||'red cat'||'%'
limit 100
 
This does not find products like "red or black cat".
How to change this query so that it returns 100 best matches for for given search string?
I read documentaton about full text search but havent found step by step solution for this.
 
Using PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
 
Should I install some contribs to 8.2 or is it better to upgrade server ?
 
Where to find step by step instructions making this work ?
 
Andrus.
 
 
 
 
 

Re: How to perform full text search

From
"Andrus"
Date:
>Parse the entry string into words (aka tokens) and assemble with the and
>operator. E.g. 'red cat' becomes 'red & cat'. >Then add vector; more info
>in articles I provide links to later in this note.
>WHERE to_tsvector ( productname || ' ' || productdescription ) @@
>to_tsquery ( 'red & cat' )

Since there were no responces for a while, I went with another solution.
Splitted search string to words like you but converted query to

select
+case when productname ilike '%red%'  then 2 else 0 end
+case when productdescription ilike '%red%'  then 1 else 0 end
+case when productname ilike '%cat%'  then 1.7 else 0 end
+case when productdescription ilike '%cat%'  then 0.7 else 0 end
from products
order by 1 desc
limit 100

This allows to define relevance.
Is my solution reasonable ?

Andrus

Re: How to perform full text search

From
John R Pierce
Date:
On 03/18/12 11:45 AM, Andrus wrote:
> select
> +case when productname ilike '%red%'  then 2 else 0 end
> +case when productdescription ilike '%red%'  then 1 else 0 end
> +case when productname ilike '%cat%'  then 1.7 else 0 end
> +case when productdescription ilike '%cat%'  then 0.7 else 0 end
> from products
> order by 1 desc
> limit 100
>
> This allows to define relevance.
> Is my solution reasonable ?

if you don't mind a full table sequential scan each time you execute
that, I suppose.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast