From: | "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | how to query against nested hstore data type |
Date: | 2014-08-25 02:05:17 |
Message-ID: | D83E55F5F4D99B4A9B4C4E259E6227CD014F78D1@AUX1EXC02.apac.experian.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
testdb=# select (each(channel)).key,(each(channel)).value from visits;
key | value
-----------------------+----------------------------------------------------------------------
www.ask.com | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
www.strawberrynet.com | {visits=>2.11221003268,pages=>4.22442006537,duration=>197980.836904}
(2 rows)
The question is how can I get visits/pages/duration separately from the value column so that I can do a sum on those metrics individually.
Thank you!
Suya
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2014-08-25 02:14:12 | Re: how to query against nested hstore data type |
Previous Message | johnlumby | 2014-08-24 21:49:31 | Re: Extended Prefetching using Asynchronous IO - proposal and patch |