Postgresql full text search

Question

0.00/5 (No votes)

See more:

We have around 20 millions of records in Postgresql database. One of the column is html document stored as Text datatype. We want Use this column in search functionality. We have converted html content to plaintext and created tokens using to_tsvector. We have implemented full text search on this token column.

1. When we search with one word and logical operators, it returns results as expected. Example:

("Jack" AND ("Jill" OR "Tom") )

2. However, when we use two or more worded search strings with logical operators, then it is not giving expected output. Example: for search string

("Jack Jill" OR "Jack Tom")

, it is not searching for “Jack Jill” together but if it finds those two words in one text it returns. Here, if the column has “Jack Ryan Mark Jill”, it will return the row but, we are expecting it to return only when it is like “Jack Jill Ryan Mark”.

Can you please help us solve this issue. Let us know if you need any details from our side.

What I have tried:

SELECT * from tablename where html_tokens(column where I am storing the token)  @@ to_tsquery((ikeyword)

Posted 11-Feb-19 3:01am

Member 10367528

Updated 11-Feb-19 3:24am

RickZeeland

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2019-02-11T03:24:00

Solution 1

You need to use the proximity operator <->
So that would be:

"Jack <-> Jill"

See examples here: Mastering PostgreSQL Tools: Full-Text Search and Phrase Search - Compose Articles[^]

Posted 11-Feb-19 3:24am

RickZeeland

Comments

Maciej Los 11-Feb-19 12:25pm

Very interesting linked article!

Member 10367528 14-Feb-19 5:33am

Hi Rick,

Thank you for your update.But i have already gone through this article but this proximity operator <-> is not working with OR and NOT operator only working with "AND"
operator on top of i have another issue with searching resumes.If one resume is having
"Project Management" and i am searching for "Project Manager" with proximity operator <-> "Project Management" resume is coming as result which is not correct.
Do you have idea how to handle this type search?

RickZeeland 14-Feb-19 5:58am

Maybe you can use ::tsvector instead of to_tsvector() to get rid of the "normalization"
See explanation here: https://stackoverflow.com/questions/24045475/postgres-to-tsvector-vs-tsvector

Member 10367528 14-Feb-19 7:11am

Hi Rick,

Is there way can we have call Please for the discussion?

RickZeeland 14-Feb-19 9:11am

That will be not much help I'm afraid as I have never used full-text search in Postgres myself :)