CODEWITHSUNDEEP
postgresqlfull-text-search
Jean Reptile
Jean Reptile

Reputation: 107

Postgresql Full Text Searching : Strange behavior of AND operator

So, I have a PostgreSQL datable with a table documents

id|fullname

I added a tsv column and update the table with an index:

 UPDATE documents SET tsv = setweight(to_tsvector(coalesce(fullname,'')), 'A');

But, when I do this query :

SELECT id, fullname, tsv
FROM documents
WHERE (tsv @@ to_tsquery('will & smith'))

In the results, there are rows with only smith and not will and smith. Like this one :

  id    |   fullname   |       tsv
1779471 | gloria smith | gloria':1A 'smith':2A

This behavior seems more like an or operator than an and.

Do you have an idea why the & operator does not work well ?

Upvotes: 1

Views: 49

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48246

Use the simple regconfig in your ts functions:

select
  *
from documents
where
  to_tsvector('simple', fullname) @@ to_tsquery('simple', 'will & smith');

Working example here http://sqlfiddle.com/#!15/8f950/1

Upvotes: 1

Related Questions