我甚至不确定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.