CODEWITHSUNDEEP
postgresqlfull-text-search
user1865027
user1865027

Reputation: 3647

how to do OR to all the words in full text search instead of AND in postgres

I have a post table which has a tsv column. Let's assume tsv is generated by title only. There are 3 rows in the post table now. Their titles are 'hello', 'world' and 'hello world'.

When I do a select query like

SELECT p.*
   FROM plainto_tsquery('hElLo WoRlD’) AS q,
        post p
   WHERE tsv @@ q
ORDER BY ts_rank_cd(p.tsv, plainto_tsquery('hElLo WoRlD')) DESC;

I would assume all three records returned with the one has 'hello world' as title being the first in the list, but the query actually only return one record. How can I have all 3 records return? and of course sorted by most relevant.

tsv is generated by setweight(to_tsvector(coalesce(new.title,'')), 'A');

and if there's a way to do both would be even better. Like what google can do "hello world" foo bar will return results with "hello world" and/or foo and/or bar

Upvotes: 0

Views: 2365

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

You should use the "or" operator in tsquery:

... WHERE tsv @@ to_tsquery('hello | world');

Upvotes: 2

Related Questions