1

I've implemented the full text search by using tsvector and tsquery. It is not working when there is a comma present in the database.

I'm using the below query to search the result and it is not fetching any row.

select ndc,ln60 
from rndc14_ndc_mstr  
where (to_tsvector(ln60) @@ plainto_tsquery('Metformin 1000'));

Below are the data present in the database with matching of above string.

enter image description here

Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
  • have you considered using similarity? `SELECT ndc,ln60,SIMILARITY(ln60, 'Metformin 1000') FROM rndc14_ndc_mstr WHERE similarity(ln60, 'Metformin 1000') > 0` it is part of the pg_trgm extension`CREATE EXTENSION pg_trgm;` – Jim Jones Dec 13 '22 at 09:26
  • I need only matching row if i'm using similarity function it is returning all the rows – Nayan Rudani Dec 13 '22 at 10:32
  • 1
    "similarity function it is returning all the rows" Change the threshold if you want more strict matching. – jjanes Dec 13 '22 at 14:45
  • 1
    You don't have a comma problem, you have a data cleanliness problem. What if it were stored as '1 000', or '1.000', or '1e3' instead? or '15.4324 gr'? – jjanes Dec 13 '22 at 14:51

1 Answers1

0

To understand why your previous query did not fetch the expected results, it is necessary to look into the behavior of PostgreSQL's plainto_tsquery() function. This function tokenizes the given plain text into words separated by &, |, !, and <-> operators. Any commas and punctuation marks are ignored during tokenization.

To force the function to consider a comma as part of the search query, you can escape it using a backslash (). However, to search for both "Rate, Fat" and "Rate Fate" is to use the | operator to combine two separate plainto_tsquery() functions, one for each search term.

Here's an example query that uses two separate plainto_tsquery() functions with the | operator to search for both "Rate, Fat" and "Rate Fate":

SELECT * FROM mytable WHERE search_vector @@ plainto_tsquery('english', 'Rate\,Fat') | plainto_tsquery('english',' Rate <-> Fate');

the full documentation of PostgreSQL https://www.postgresql.org/docs/14/functions-textsearch.html

AmrShams07
  • 96
  • 1
  • 7