CODEWITHSUNDEEP
postgresql
Son
Son

Reputation: 1050

How to build tsquery with alternative in postgres full text search

I have configured full text search on some tables in postgres DB. When I try to build tsquery with more than 1 word I get words in tsquery with & (AND) as delimiter. In effect I can't find results where is only one of words what I asked.

Example:

| ID | Name    | Description         | Rank |
|----|---------|---------------------|------|
| 1  |Somethink|This is very strange | 0.2  |
| 2  |Other    |This is strange a bit| 0    |

When I ask for "very strange" my tsquery looks like that 'very' & 'strange' , and ranks (ts_rank_cd) for that tsquery is more than 0 only for ID == 1, for ID == 2 is 0.

My goal is to achieve rank > 0 for 2nd sentence. How to achieve this?

Upvotes: 1

Views: 1695

Answers (2)

Mark McKelvy
Mark McKelvy

Reputation: 3536

You can create an OR version of plainto_tsquery by doing something along the lines of:

select replace(plainto_tsquery('foo bar')::text, '&', '|')::tsquery;

Similar to what klin advised but takes advantage of the stemming capabilities of plainto_tsquery.

As an aside, I believe very is a stop word so it won't be included in any to_tsquery or plainto_tsquery output.

Upvotes: 5

klin
klin

Reputation: 121764

Use OR instead of AND in the tsquery:

with data(d) as (
values
    ('This is very strange'),
    ('This is strange a bit')
)
select d, ts_rank_cd(to_tsvector(d), 'strange | very'::tsquery)
from data

           d           | ts_rank_cd 
-----------------------+------------
 This is very strange  |        0.2
 This is strange a bit |        0.1
(2 rows)

You can automatically change a user input using the function replace(), e.g.:

with data(d) as (
values
    ('This is very strange'),
    ('This is strange a bit')
),
query (query) as (
values
    ('very & strange')
)
select d, ts_rank_cd(to_tsvector(d), replace(query, '&', '|')::tsquery)
from data
cross join query;

Upvotes: 1

Related Questions