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 (…

Accepted Answer

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.

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

Solution

Problem