19

Is postresql capable of doing a full text search, based on 'half' a word? For example I'm trying to seach for "tree", but I tell postgres to search for "tr".

I can't find such a solution that is capable of doing this.

Currently I'm using

 select * from test, to_tsquery('tree') as q where vectors @@ q ;

But I'd like to do something like this:

 select * from test, to_tsquery('tr%') as q where vectors @@ q ;
wpp
  • 7,093
  • 4
  • 33
  • 65
Grezly
  • 488
  • 1
  • 6
  • 13

4 Answers4

27

You can use tsearch prefix matching, see http://www.postgresql.org/docs/9.0/interactive/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES

postgres=# select to_tsvector('tree') @@ to_tsquery('tr:*');
 ?column? 
----------
 t
(1 row)

It will only work for prefix search though, not if you want partial match at any position in the word.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
  • 1
    That is kind of the direction i'm looking for. But i'm interested in the word(s) that are equal to 'tr:*' (in your example). Is it possible to only get those words back? – Grezly Dec 28 '10 at 06:45
  • Well, to build on what you wrote, something like SELECT * FROM test WHERE vectors @@ to_tsquery('tr:*') should do it. Or do you mean you want to know which individual words inside the tsvector field matched? I don't think you can do that... – Magnus Hagander Dec 28 '10 at 08:14
  • i need indeed the fields/words that matched my input. – Grezly Dec 28 '10 at 09:11
12

Sounds like you simply want wildcard matching.

  • One option, as previously mentioned is trigrams. My (very) limited experience with it was that it was too slow on massive tables for my liking (some cases slower than a LIKE). As I said, my experience with trigrams is limited, so I might have just been using it wrong.

  • A second option you could use is the wildspeed module: http://www.sai.msu.su/~megera/wiki/wildspeed (you'll have to build & install this tho).

The 2nd option will work for suffix/middle matching as well. Which may or may not be more than you're looking for.

There are a couple of caveats (like size of the index), so read through that page thoroughly.

catchdave
  • 9,053
  • 2
  • 27
  • 16
  • 4
    hasn't the wildspeed stuff been part of PostreSQL core since 2008...? – simon Oct 10 '13 at 01:28
  • any answers to the question above? – Rodrigo Aug 26 '14 at 18:35
  • wildspeed hasn't been updated since 2008, so unless it's part of the core already I would use the [pg_trgrm](http://www.postgresql.org/docs/current/interactive/pgtrgm.html) extension. – RichVel Apr 07 '16 at 05:43
5
select * from test, to_tsquery('tree') as q 
where vectors @@ q OR xxxx LIKE ('%tree%')

':*' is to specify prefix matching.

uzaif
  • 3,511
  • 2
  • 21
  • 33
Lecky Lao
  • 359
  • 3
  • 7
2

It can be done with trigrams but it's not part of tsearch2.

You can view the manual here: http://www.postgresql.org/docs/9.0/interactive/pgtrgm.html

Basically what the pg_tgrm module does is split a word in all it's parts so it can search for those separate parts.

Wolph
  • 78,177
  • 11
  • 137
  • 148