From: | "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: how to query against nested hstore data type |
Date: | 2014-08-27 01:50:30 |
Message-ID: | D83E55F5F4D99B4A9B4C4E259E6227CD014F81AC@AUX1EXC02.apac.experian.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Tuesday, August 26, 2014 11:58 PM
To: Huang, Suya; pgsql-general(at)postgresql(dot)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(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
==========================================================================
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-08-27 02:41:47 | Re: how to query against nested hstore data type |
Previous Message | Arze, Cesar | 2014-08-27 00:51:24 | Re: Postgres 9.1 issues running data directory from VMware shared folder |