Re: Prefix search on all hstore values

On Wed, Nov 27, 2013 at 6:49 PM, Albert Chern <albert.chern@gmail.com> wrote:
> I have an hstore column that stores a string in several arbitrary languages,
> so something like this:
>
> "en" => "string in english", "zh" => "string in chinese", "fr" => "string in
> french"
>
> Is it possible to construct an index that can be used to determine if a
> query string is a prefix of ANY of the values in the hstore?  From reading
> the documentation the closest I've gotten is a gin index after converting
> the values to an array, but that doesn't seem to work with prefix searching.
> Any pointers would be much appreciated!

The idea is to de-normalize the hstore_column to an assisting table
with 2 columns: original_record_id, hstore_column_value. And to create
a btree index on hstore_column_value that will effectively be used in
prefix search.

--
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.ru@gmail.com