How to append prefix match to tsquery in PostgreSQL

Question

I'm trying to utilize the full text search feature of PostgreSQL, particularly when user types in some search text, I would like to display him results with an assumption that the last word is incomplete.

For that purpose the "*" wildcard character needs to be attached to the last tsquery lexeme. E.g. if the user types in "The fat ra" the tsquery should be 'fat' & 'ra':*.

If I append the wildcard to the input string and parse it with plainto_tsquery function then the wildcard is removed plainto_tsquery("The fat ra" || ":*") => 'fat' & 'ra'.

Constructing a tsquery manually with to_tsquery function requires a lot modifications to the string (such as trim spaces and other special characters, replace spaces with the ampersand character) to make the function accept it.

Is there an easier way to do that?

Haber

Answer 1

You can make the last lexeme in a tsquery a prefix match by casting it to a string, appending ':*', then casting it back to a tsquery:

=> SELECT ((to_tsquery('foo <-> bar')::text || ':*')::tsquery);
      tsquery      
-------------------
 'foo' <-> 'bar':*

For your usecase, you'll want to use <-> instead of & to require the words to be next to each other. Here's a demonstration of how they're different:

=> SELECT 'foo bar baz' @@ tsquery('foo & baz');
 ?column? 
----------
 t
(1 row)

=> SELECT 'foo bar baz' @@ tsquery('foo <-> baz');
 ?column? 
----------
 f
(1 row)

phraseto_tsquery makes it easy to have specify many words that have to be next to each other:

=> SELECT phraseto_tsquery('foo baz');
 phraseto_tsquery 
------------------
 'foo' <-> 'baz'

Putting it all together:

=> SELECT (phraseto_tsquery('The fat ra')::text || ':*')::tsquery;
     tsquery      
------------------
 'fat' <-> 'ra':*

Depending on your needs, a simpler way might be to build a tsquery directly with a string then a cast:

=> SELECT $$'fat' <-> 'ra':*$$::tsquery;
     tsquery      
------------------
 'fat' <-> 'ra':*

Effluvium

Nice solution, but this can result in errors with some user provided search strings. For example, if the query string is empty then SELECT (phraseto_tsquery('')::text || ':*')::tsquery; will raise syntax error in tsquery: ":*". The same error will be raised if the query only contains stopwords like SELECT (phraseto_tsquery('The and a')::text || ':*')::tsquery;. – Fecundate