-----Original Message----- From: Adrian Klaver [mailto:adrian.kla...@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.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 ========================================================================== 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
