Unable to fetch a word containing spaces using to_tsvector and to_tsquery

Question

I want to fetch a word entered by a user in the database:

select name 
from users
where to_tsvector(name) @@ to_tsquery('$word:*')

I mention word = alain john smith, when I run the query I got this error:

syntax error in tsquery: "alain john smith"

The plainto_tsquery cannot recognize Boolean operators, weight labels, or prefix-match labels in its input Parsing queries

How can I fix that?

Answer 1

A tsquery cannot contain alain john smith, since the parser will split this into three words.

If you have PostgreSQL 9.6 or later, you can use

to_tsquery('alain <-> john <-> smith')

which makes use of the “followed by” operator <->.

In older versions, the best you can do is

to_tsquery('alain & john & smith')

which will match strings containing all three words, but not necessarily adjacent or in this order.