Thanks for the suggestions! My requirements can be relaxed to full text search, but the problem I had with that approach is I have strings in Chinese, and postgres doesn't seem to support it. Calling to_tsvector() on Chinese characters always returns an empty vector.
A separate table will definitely work, but I was hoping for something more straightforward. I'll use that approach if necessary though. On Thu, Nov 28, 2013 at 4:51 PM, Sergey Konoplev <gray...@gmail.com> wrote: > On Thu, Nov 28, 2013 at 12:44 AM, Teodor Sigaev <teo...@sigaev.ru> wrote: > > Full-text search has this feature. > > > > # select to_tsvector('en_name=>yes, fr_name=>oui'::hstore::text) @@ > 'en:*'; > > ?column? > > ---------- > > t > > > > or (index only keys) > > > > select to_tsvector(akeys('en_name=>yes, fr_name=>oui'::hstore)::text) @@ > > 'en:*'; > > ?column? > > ---------- > > t > > > > To speed up this queries you use functional indexes. > > It wont work. The OP needs to search by values prefixes, not by any > separate word in the hstore. > > # select to_tsvector('en_name=>"oh yes", fr_name=>oui'::hstore::text) @@ > 'ye:*'; > ?column? > ---------- > t > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > http://www.linkedin.com/in/grayhemp > +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 > gray...@gmail.com >