Menu
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 ;
402
Yes, You Can Keep Full-Text Search in Postgres You can get even deeper and make your Postgres full-text search even more robust, by implementing features such as highlighting results, or writing your own custom dictionaries or functions.
The Good Ol' Text Search. You're probably familiar with pattern search, which has been part of the standard SQL since the beginning, and available to every single SQL-powered database: SELECT column_name FROM table_name WHERE column_name LIKE 'pattern'; That will return the rows where column_name matches the pattern .
A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word (see Chapter 12 for details).
Go to any cluster and select the “Search” tab to do so. From there, you can click on “Create Search Index” to launch the process. Once the index is created, you can use the $search operator to perform full-text searches.
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.
85
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.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
