How do you do phrase-based full text search in postgres that takes advantage of the full-text index?

Question

Let's say you have a postgres 8.3 table as follows:

CREATE TABLE t1 (body text, body_vector tsvector);

I want to be able to search it for phrases using the full text index (GiST, GiN or both on the tsvector column). The best workaround I've been able to find is to first do the full text search on both words (boolean AND) and then do a like comparison on the body for the phrase. Of course, this fails to capture any stemming or spell-checking that postgres' full-text search does for you. An example of this is if I'm searching for the phrase 'w1 w2', I'd use:

SELECT * FROM t1 WHERE body_vector @@ 'w1 & w2'::tsquery AND body LIKE 'w1 w2';

Is there a way to do this where you don't have to resort to searching on the text column?

Answer 1

If you want exact phrase matching, that's the way to do it. You can also try WHERE body_vector @@ plainto_tsquery('w1 w2'), and then order it by ranking. (the point being that the hits where the words are right next to each other should end up on top)

Answer 2

Update: PostgreSQL 9.6 text search supports phrases

select
  *
from (values
  ('i heart new york'),
  ('i hate york new')
) docs(body)
where
  to_tsvector(body) @@ phraseto_tsquery('new york')

(1 row retrieved)

or by distance between words:

-- a distance of exactly 2 "hops" between "quick" and "fox"
select
  *
from (values
  ('the quick brown fox'),
  ('quick brown cute fox')
) docs(body)
where
  to_tsvector(body) @@ to_tsquery('quick <2> fox') 

(1 row retrieved)