From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | Albert Chern <albert(dot)chern(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Prefix search on all hstore values |
Date: | 2013-11-28 08:38:15 |
Message-ID: | CAL_0b1sOYq7ANZrUUe4nruT+nTiL4Qxw3wtK_8u+oVLTTUOzmg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Nov 27, 2013 at 6:49 PM, Albert Chern <albert(dot)chern(at)gmail(dot)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(dot)ru(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2013-11-28 08:44:11 | Re: Prefix search on all hstore values |
Previous Message | David Johnston | 2013-11-28 07:35:01 | Re: unnest on multi-dimensional arrays |