Postgresql full text search part of words

Question

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 ;

Answer 1

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.

Answer 2

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.