我甚至不确定Postgres的HStore数据类型可以包含嵌套散列,如果可以的话,如何插入它们？
这是我迄今为止所尝试过的：
-- 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 */
如果不可能的话,现在可以使用嵌套散列函数来接受标准/成语 – 也许将它们分开并使用id来引用它们？
对此的任何帮助将不胜感激.
从
fine manual：
Keys and values are simply text strings.
所以,不,你不能在商店里使用一个商店作为价值.如果你看看hstore operators和functions,你会看到他们都使用文本值.
我不知道任何标准的方法来伪装嵌套哈希.我怀疑你必须构造密钥(a.b => c for a => b => c),那么你可以这样做：
select slice(doc,array['a.b','a.c']) from my_store where doc ?& array['a.b','a.c']
抓取每个文档的“a”片,其中包含{b => …,c => …}“sub-hash”.
还有一个JSON type可能更适合您的需求.但是,你必须等待它,我不知道what the final implementation will look like.
