15

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?

ealdent
  • 3,677
  • 1
  • 25
  • 26

2 Answers2

13

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)

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
10

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)
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • 1
    Maybe I didn't catch somthing, but doesn't this require the input string to contain the searched phrase between single quotes? How could anyone use it in general, when the phrase comes from auser input, and the document comes from a database column? – Zoltán Tamási Apr 11 '15 at 16:24
  • 1
    This is still not "phrase searching". That is only working since your tsvector phrase 'new york' is wrapped in single-quotes. – soyayix Sep 22 '16 at 23:42
  • Yes both function does - but your tsvector string in most cases will not have single-quotes on them - if it does then what is the search phrase for.. I have a postgreSQL 9.4 and the query you have works - this is not only specific to 9.6. select 'i heart new york city'::tsvector @@ 'new'::tsquery, --true 'i heart new york city'::tsvector @@ 'new & york'::tsquery, --true 'i heart new york city'::tsvector @@ '''new york'''::tsquery --false – soyayix Sep 23 '16 at 00:35
  • 2
    Note these comments about single quotes are no longer relevant to the edited answer, which correctly demonstrates the use of phraseto_tsquery which was added in 9.6 to support precisely this use case. – Yetanotherjosh Sep 15 '17 at 21:18
  • 1
    Instead of using phraseto_tsquery, you can just substitute <-> for all spaces and stick to to_tsquery. So in this case it would end up being a query on quick<->brown<->fox, and then you can just skip the additional LIKE filter. – pmarreck Oct 05 '17 at 16:22