I have a PostgreSQL used to index text content. The SearchVector column is created successfully using the following code The content looks like the following: But if I try to run a query to get plurals or singular of manual (in Italian: manuale is one, manuali are 2 or more) it fails: return nothing return nothing It only returns the

Full text search failure on PostgreSQL

I have a PostgreSQL used to index text content. The SearchVector column is created successfully using the following code

UPDATE public."DocumentFiles"
SET "SearchVector" = setweight(to_tsvector('pg_catalog.italian', coalesce("DocumentFileName", '')), 'A') 
   || setweight(to_tsvector('pg_catalog.italian', coalesce("DocumentFileDescription", '')), 'B') 
   || setweight(to_tsvector('pg_catalog.italian', coalesce("DocumentFileContentString", '')), 'B')
WHERE "DocumentFileID" = 123;

The content looks like the following:

'011989':1A '5':7A 'cdp':2A 'contonu':10A 'elettr':6A 'grupp':8A 'impiant':5A 'manual':3A 'uso':4A

But if I try to run a query to get plurals or singular of manual (in Italian: manuale is one, manuali are 2 or more) it fails:

SELECT "DocumentFileID"
FROM public."DocumentFiles"
where "SearchVector"::tsvector @@ 'manuali'::tsquery;

return nothing

SELECT "DocumentFileID"
FROM public."DocumentFiles"
where "SearchVector"::tsvector @@ 'manuale'::tsquery;

return nothing

It only returns the record if I write exactly what is written in the searchvector field:

SELECT "DocumentFileID"
FROM public."DocumentFiles"
where "SearchVector"::tsvector @@ 'manual'::tsquery;

What’s wrong with it?

Answer

The problem is probably that the parameter default_text_search_configuration is not set to italian, so that a different stemming algorithm is used.

Be explicit and use to_tsquery('italian', 'manuali') rather than 'manuali'::tsquery.

Advertisement

Answer