Find partial match with textsearch query

Question

I have the following query that I did hard looking on this site:

SELECT *
FROM   renns
WHERE
    (
        to_tsvector('english', coalesce(numar_admin, '')) || 
        to_tsvector('english', coalesce(nume_strada, '')) || 
        to_tsvector('english', coalesce(nume_prenume, ''))|| 
        to_tsvector('english', coalesce(bloc, '')) ||
        to_tsvector('english', coalesce(apartament, '')) 
    ) @@ plainto_tsquery('english', '$q')

It works if I write in the variable $q all the text that appears in one of the columns present in the query. For example: cherry

I would like to write only: cher and return all rows fields in which it finds this string: "cher".

Answer 1

Postgres text search supports prefix matching:

SELECT *
FROM   renns
WHERE (
   to_tsvector('english', coalesce(numar_admin, '')) || 
   to_tsvector('english', coalesce(nume_strada, '')) || 
   to_tsvector('english', coalesce(nume_prenume, ''))|| 
   to_tsvector('english', coalesce(bloc, '')) ||
   to_tsvector('english', coalesce(apartament, '')) 
) @@ to_tsquery('simple', $q || ':*');

($q being a single word.)
This can use an index on your expression:

(to_tsvector('english', coalesce(numar_admin , '')) || 
 to_tsvector('english', coalesce(nume_strada , '')) || 
 to_tsvector('english', coalesce(nume_prenume, '')) || 
 to_tsvector('english', coalesce(bloc        , '')) ||
 to_tsvector('english', coalesce(apartament  , '')))

Using the 'simple' dictionary disables stemming. If you want stemming, use the 'english' dictionary like you had before.

"caisului 498:*" the "CAISULUI" string is from one column and the number from another column of the renns table

To pass multiple lexemes, apply 'english' stemming and do prefix matching for all of them:

@@ (to_tsquery('english', column_a || ':*') &&
    to_tsquery('english', column_b || ':*') -- more?   
   )

Detailed explanation:

• Get partial match from GIN indexed TSVECTOR column

Prefix matching matches at the start of lexemes. For any partial match, you can use LIKE or regular expression matches (~) in combination with a trigram index. Different approach! Requires a different query and a different index! See:

• How to create an index to speed up an aggregate LIKE query on an expression?
• Faster query with pattern-matching on multiple text fields
• How is LIKE implemented?
• Pattern matching with LIKE, SIMILAR TO or regular expressions

There are smart ways to integrate both. The manual has an example.