[Postgresql NLS support] : Help on using NLS , Custom dictionary to enhance our website search functionality

From: Nivedita Kulkarni <nkulkarni(at)genius-minds(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: [Postgresql NLS support] : Help on using NLS , Custom dictionary to enhance our website search functionality
Date: 2015-05-28 07:14:14
Message-ID: 1047152826.203938.1432797254432.JavaMail.yahoo@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-novice

Hello All,

We have newbie to Postgresql.
Background:
We have site hosted on Ruby on Rails using Postgresql database.It is a eCommerce site and for which we need to provide the NLS supported Search functionality to help end users while searching by using Synonyms, related word , Plurals and Singular , Stop words etc.

Problem 
As per our analysis we found that with Postgresql NLS it possible to do following with any custom changes:

1. Participles (help,helps,helped,helping)

2. Multi-word (search results)

3. Single word (results)

4. Plurals and Singular (s,es)

5. Punctuation's (.,;,!,:,')

6. Stop words (And, or, as , an, but)

Reading the documentation for Custom dictionary, We tried to use the Custom dictionary postgresql/9.3/tsearch_data  and added following to test :

buy purchase

big enormous

mammoth elephant

indices index*

But we found that when we search for word "Purchase" we get search result for "Purchase" and "Buy" both.

But when we search for "Buy" then we don't get search result for "Purchase".

We are using following query to using which we found that transitive synonym search is not working for us. 

SELECT "products".* FROM "products" WHERE (((to_tsvector('english', coalesce("products"."description"::TEXT, ''))) @@(to_tsquery('english', ''' ' || 'purchase' || ' '''))));

I think transitive support will be basic function and Postgresql database must have provided.  Please let us know if some setting or configuration changes are needed to enable this feature. 

Also as per our finding, following are points on which it seems there is no direct way in Postgresql:1. Related words  (Pushchair buggies)2. Near by words (Pen , Pencil, Eraser)3. Synonyms (buy<=>purchase,big<=>enormous)4. Spelling Mistake

We look forward for feedback / suggestions our problem or suggest any alternative solution, how this can be done ? 

Thanks in advance  Thanks and regards, 
Nivedita Kulkarni

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Chris Mair 2015-05-28 08:37:09 9.4.2 -> 9.4.3
Previous Message John R Pierce 2015-05-28 04:57:43 Re: [SQL] extracting PII data and transforming it across table.

Browse pgsql-hackers by date

  From Date Subject
Next Message Noah Misch 2015-05-28 07:27:21 Re: pg_upgrade resets timeline to 1
Previous Message Stephen Frost 2015-05-28 05:38:14 Re: [COMMITTERS] pgsql: Add pg_audit, an auditing extension

Browse pgsql-novice by date

  From Date Subject
Next Message Vincenzo Campanella 2015-05-28 09:19:58 Re: Drop or disable or bypass "_return" rule on select on a view.
Previous Message Shashwat Arghode 2015-05-28 06:23:45 Drop or disable or bypass "_return" rule on select on a view.