Re: 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(dot)klaver(at)aklaver(dot)com]
> Sent: Monday, August 25, 2014 11:42 PM
> To: Huang, Suya; pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com