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 ; 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.
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.
select * from test, to_tsquery('tree') as q where vectors @@ q OR xxxx LIKE ('%tree%') ':*' is to specify prefix matching.
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.
