11

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.

ian
  • 12,003
  • 9
  • 51
  • 107

2 Answers2

15

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 1
    If this doesn't give you want, you might be able to cast an hstore into text and then escape the quotes to get a string which could be saved as the value in an hstore, but it probably wouldn't be that convenient to use. You might want to consider normalizing the data a bit more. – kgrittn Jun 17 '12 at 19:59
  • Thanks for clearing that up, I appreciate it. I did read that page in the manual (and it is a fine manual) but it didn't even have an example of an insert, so wondered perhaps if there were more complex examples that had been left out too. @kgrittn I think the casting will be the route I end up taking, I don't need normalised data in this instance, (just a document as a blob really), unless that's the only way to get the document into the database! – ian Jun 18 '12 at 03:41
1

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.

Tom
  • 1,007
  • 12
  • 13