Search code examples
sqldatabasepostgresqlfull-text-search

PostgreSQL full text search not finding some words it should find

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?

Solution

  • 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.