5

As of Postgres 8.4 the database fts does not support exact phrase match, nor does it support proximity match if given 2 terms. For example, there is no way to tell Postgres to match on content that have word #1 which is in a specified proximity of word #2. Any one know the plan of Postgres and possibly which version will phrase and proximity match be supported?

hko19
  • 1,054
  • 4
  • 16
  • 25
  • The PostgreSQL project doesn't have a roadmap. Things get done on the basis of who actually decides to do the work. So no idea, no possible answer. – Chris Travers Oct 02 '12 at 01:22

2 Answers2

4

PostgreSQL 9.6 text search supports phrases now

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
    The quest is about Proximity search, for example: Is the word 'the' and the word 'fox' is less than 2 words apart? – hko19 Jul 30 '14 at 23:54
  • Can someone explain me why would someone want proximity search? I would like PostgreSQL to rank documents where keywords are closer higher, but why would I want the actual answer to how close they were? – Janko Feb 05 '15 at 13:53
  • You can use it as a fallback when phrase match does not yield results. For instance, given the doc `hip hop is music`, if you search for `"hip hop music"` it does not match, but if you search for `hip <1> hop <1> music` it will match and is still a phrase search, approximate phrase. It also depends if you have a block list etc... but that is the idea. – amirouche Feb 13 '21 at 19:05
2

http://linuxgazette.net/164/sephton.html

<snip>

Search Vectors

How does one turn document content into an array of lexemes using the parser and dictionaries? How does one match a search criterion ti body text? PostgreSQL provides a number of functions to do this. The first one we will look at is to_tsvector().

A tsvector is an internal data type containing an array of lexemes with position information. The lexeme positions are used when searching, to rank the search result based on proximity and other information. One may control the ranking by labelling the different portions which make up the search document content, for example the title, body and abstract may be weighted differently during search by labelling these sections differently. The section labels, quite simply A,B,C & D, are associated with the tsvector at the time it is created, but the weight modifiers associated with those labels may be controlled after the fact.

</snip>

For full-phrase searching, see here.

The Postgresql website does not have a roadmap. Instead, you are referred to the Open Issues page. At the moment, this page makes no mention of full-phrase searching.

Community
  • 1
  • 1
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • 3
    So the short answer is that there is no proximity match (as in NEAR() in Oracle) and there's is no efficient phrase matching for Postgres :( – hko19 May 27 '10 at 23:12
  • Is that what I said? If you're looking for the short path, i.e. a keyword in the SQL language, then no. But that doesn't mean you can't do it with the tools outlined in the article. – Robert Harvey May 28 '10 at 00:12
  • The article mentions 'phrase search' needs a 'like' (or 'ilike') operator to scan through the limited result set which may be expensive. What other tools in particular that I may overlook? – hko19 Nov 09 '10 at 23:10