postgres search query error if space used

Question

I'm following along with Ryan Bates' Railscast on full text search with Postgres, however, he's using postgres 9.1 and I'm using 9.2. He builds the following query to execute a search. It works for me if my query is a single word, such as "superman" but if it's two words, such as dc comics, or super man, I'm getting this error, which being just new to postgres I can't figure out how to fix. Can you assist?

PG::Error: ERROR:  syntax error in tsquery: "super man"
LINE 1: ...articles"  WHERE (to_tsvector('english', name) @@ 'super man...
                                                             ^
: SELECT  "articles".* FROM "articles"  WHERE (to_tsvector('english', name) @@ 'super man' or to_tsvector('english', content) @@ 'super man') ORDER BY       ts_rank(to_tsvector(name), plainto_tsquery('super man')) +
      ts_rank(to_tsvector(content), plainto_tsquery('super man'))
 desc LIMIT 3 OFFSET 0

query from Article.rb

 def self.text_search(query)
    if query.present?
      rank = <<-RANK
      ts_rank(to_tsvector(name), plainto_tsquery(#{sanitize(query)})) +
      ts_rank(to_tsvector(content), plainto_tsquery(#{sanitize(query)}))
    RANK

  where("to_tsvector('english', name) @@ :q or to_tsvector('english', content) @@ :q", q: query).order("#{rank} desc")

    else
      scoped
    end
  end

Answer 1

@@ is used to compare a tsvector with a tsquery. You are trying to compare a tsvector with something that is not a valid tsquery.

'superman' is of type text and should really be wrapped in a call to to_tsquery(). However it looks like postgres has tried to help you out and coerced it into a tsquery for you, and to_tsquery('superman') is a valid query.

'super man' is of type text and should really be wrapped in a call to to_tsquery(). Postgres has failed to coerce it into a tsquery for you, since to_tsquery('super man') is not a valid query. A valid tsquery must have boolean operators like & or | to tell the query how to treat the words. 'super & man' would probably work.

To save you having to write queries for simple cases of AND-style queries, plainto_tsquery makes this a little easier. In your case wrap your :q param in a call to plainto_tsquery

plainto_tsquery(:q)