Is it possible, and what is the syntax for a nested hash in Postgresql's HStore type?

Question

I'm not even sure that Postgres' HStore data type can contain nested hashes, and if they can, how to insert them?

Here's what I've tried so far:

-- Database: test1

-- DROP DATABASE test1;
/*
CREATE DATABASE test1
  WITH OWNER = iainuser
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_GB.UTF-8'
       LC_CTYPE = 'en_GB.UTF-8'
       CONNECTION LIMIT = -1;
*/
/* create extension hstore; */
/*drop table my_store;*/
/*
create table my_store (
  id serial primary key not null,
  doc hstore
);

CREATE INDEX my_store_doc_idx_gist
  ON my_store
  USING gist
  (doc);
*/
/* select doc from my_store; */
/*
insert into my_store (doc) values ( '"a" => "1"' );
select doc -> 'a' as first_key from my_store; -- returns "1"
*/

/* insert into my_store (doc) values ( '"b" => "c" => "3"' ); -- doesn't work */
/* insert into my_store (doc) values ( '"b" => ("c" => "3")' ); -- doesn't work */
/* insert into my_store (doc) values ( '"b" => hstore("c" => "3")' ); -- doesn't work */
/* insert into my_store (doc) values ( '"b"' => hstore("c" => "3")' ); -- doesn't work */
/* insert into my_store (doc) values ( "b"=>'"c"=>"3"'::hstore ); -- doesn't work */

If it's not possible, is there a current accepted standard/idiom for working with nested hashes - perhaps pull them apart and refer to them using id's?

Any help with this would be much appreciated.

Answer 1

From the fine manual:

Keys and values are simply text strings.

So, no, you can't use an hstore as a value in an hstore. If you look at the hstore operators and functions you'll see that they all work with text values.

I don't know of any standard approach to faking nested hashes. I suspect you'd have to structure the keys (a.b => c for a => b => c), then you could so things like this:

select slice(doc, array['a.b', 'a.c'])
from my_store
where doc ?& array['a.b', 'a.c']

to grab the "a" slice of each doc that has the {b => ..., c => ...} "sub-hash".

There's also a JSON type coming up that might be better suited to your needs. But, you'll have to wait for it and I'm not sure what the final implementation will look like.

Answer 2

If anyone who sees this happens to be using ActiveRecord, Nested Hstore lets you store nested hashes in an hstore. It serializes the hstore values using JSON and supports a number of other data structures as well.