Re: how to query against nested hstore data type - Mailing list pgsql-general

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Tuesday, August 26, 2014 11:58 PM
To: Huang, Suya; pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to query against nested hstore data type

On 08/25/2014 06:05 PM, Huang, Suya wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Monday, August 25, 2014 11:42 PM
> To: Huang, Suya; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] how to query against nested hstore data type
>
> On 08/24/2014 07:05 PM, Huang, Suya wrote:
>> Hi,
>>
>> It's my test DB, the version is PostgreSQL 9.4beta2. I'm testing with
>> nested hstore feature.
>>
>> testdb=# \d+ test
>>
>>                            Table "public.test"
>>
>> Column |  Type  | Modifiers | Storage  | Stats target | Description
>>
>> --------+--------+-----------+----------+--------------+-------------
>>
>> id     | text   |           | extended |              |
>>
>> stats  | hstore |           | extended |              |
>>
>> testdb=# select * from test;
>>
>>         id       |
>>
>> stats
>>
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+-----------------------------------------------------
>> ---------------+-
>> ---------------+----------------------
>>
>> 2b8ea99d60b30 |
>> "www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=
>> >
>> 197980.836904}",
>> "www.strawberrynet.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"
>>
>> (1 row)
>
> So how did you get the above values in?
>

The only way I could replicate what you show is:

test=> \d hstore_test
     Table "public.hstore_test"
    Column   |  Type   | Modifiers
------------+---------+-----------
  id         | integer |
  hstore_fld | hstore  |



test=> insert into hstore_test values (1, hstore('www.ask.com',
'{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}'));
INSERT 0 1


test=> select * from hstore_test ;
-[ RECORD 1
]-------------------------------------------------------------------------------------
id         | 1
hstore_fld |
"www.ask.com"=>"{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"


test=> select (each(hstore_fld)).key,(each(hstore_fld)).value from
hstore_test ;
-[ RECORD 1 ]---------------------------------------------------------------
key   | www.ask.com
value | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}

If that is indeed what you did then you as far as I can tell you do not
have a nested hstore. Instead you have a key "www.ask.com" and a string
value "{visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}"

Where the => in the string value is decoration not functional. If you
want nesting then you will need to use the array or JSON types.


--
Adrian Klaver
adrian.klaver@aklaver.com

==========================================================================
Thanks Adrian,

I've figured out, have to apply a separate patch to query out of nested hstore.
http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary