Re: relaxed full text search? - Mailing list pgsql-novice

Yes I have done full text searching on a few different databases and I haven't found one that really does the right thing out of the box. As a example the function, plainto_tsquery, is much closer to getting me what I want than what I have seen on SQL Server but it isn't quite enough. Why oh why isn't there a plaintto_tsquery function that will take a string an let me use something closer to google like syntax is annoying. I haven't looked at Oracle lately.  That being said I think the PostgreSQL model and documentation pushes you in the right direction much better than most. Postgresql docs encourage you to think of searching on a "document" which is really just a single column in a single table. In SQL Server it looks so dam easy until you start to play with it and then you realize, after quite a bit, especially if you want to full text multiple related tables on multiple columns, that you really need to bring the data together and create a single table with a single document column. To answer your particular question I think you are looking for something like:

...to_tsquery('salmon:*')...

Here is the documentation:

     http://www.postgresql.org/docs/9.4/static/textsearch-controls.html

Since users don't like to enter in search strings like 'salmon:*' I think you will end up writing a function that parses the input to create search stings like 'salmon:*'  Frankly on a medium sized data set automatically wild carding the end of strings isn't a big deal depending also on the number of users using the database etc. etc. 

--JayR

---

From: Steven Marcus <steven@silvertrak.com.au>
To: pgsql-novice@postgresql.org
Sent: Thursday, February 4, 2016 6:17 PM
Subject: [NOVICE] relaxed full text search?

Hello,

My first implementation of full text search is generally working -- but I'd like to be able to relax the matching rules.

For example, searching for "salmon" does not find "salmonella" as configured out of the box for postgresql 9.3.10.

Here's my, probably naive, query:

SELECT * FROM table WHERE tsv @@ plainto_tsquery('salmon');

I've read the docs and searched via google -- and learnt a lot.

But I don't understand how to bend the full text search feature to do what I want?

Thanks for any help,
Steven

--

Steven Marcus

Software Development Manager

P:  +61 2 9007 5600

E:  steven@silvertrak.com.au

A: 10 Addison Road Marrickville NSW 2204

www.silvertrak.com.au