If I do a:

select to_tsvector('Angel of Mercy')

I get a 'angel':1 'mercy':3 'of':2 as a result of this query. So when I do a:

select to_tsvector('Angel of Mercy') @@ to_tsquery('Mercy') 

I get a true as a result. Indeed, "mercy" is in the vector.

of is a stop word, but I guess it is present because I didn't inform the language, english in that case. When I do it:

select to_tsvector('english', 'Angel of Mercy')

I get a 'angel':1 'merci':3. of is not present anymore, and this is expected behavior. "Mercy" gets transformed into merci lexeme, and this is understandable. But when I do a:

select to_tsvector('english', 'Angel of Mercy') @@ to_tsquery('Mercy') 

I get [] as result (which I think it should be false as I read in the docs, but whatever). So, searching "Mercy" in a table that have an entry like "Angel of Mercy" did not retrieve any results.

Is this the desired behavior? What am I missing here? How can I retrieve "Angel of Mercy" querying by "mercy", "merciful", "mercilessly" and other similar words?

CodePudding user response：

Your config setting must agree between the vector and the query processing. we don't know what your default_text_search_config is set to, but clearly it is not english.

select to_tsvector('english', 'Angel of Mercy') @@ to_tsquery('english','Mercy') 

Note the 'Mercilessly' does not get stemmed "correctly" even in english setting.