On 08/25/2014 06:05 PM, Huang, Suya wrote:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.kla...@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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
