Обсуждение: unnesting hstore data

Hi,

I'm new to hstore type and I couldn't figure out how to use
each(hstore) so I am using akeys() and avals() along with unnest().

Here is a simple example:

db=> CREATE TABLE pktest (id int8 PRIMARY KEY, tags hstore) ;
CREATE TABLE

db=> INSERT INTO pktest VALUES (1, '"key 1"=>"value 1","key 2"=>"value
2","key 100"=>"value 100","key 5150"=>"value 5150"');
INSERT 0 1

db=> INSERT INTO pktest VALUES (2, '"key 1"=>"value 1","key 2"=>"value
2","key 99"=>"value 99","key 100"=>"value 100","key 5150"=>"value
5150",name=>"id 2"');
INSERT 0 1

db=> SELECT id,tags->'name' "name",
db->        unnest(akeys(tags)) "key",
db->        unnest(avals(tags)) "val"
db->   FROM pktest
db->   ORDER BY id,key;
 id | name |   key    |    val
----+------+----------+------------
  1 |      | key 1    | value 1
  1 |      | key 100  | value 100
  1 |      | key 2    | value 2
  1 |      | key 5150 | value 5150
  2 | id 2 | key 1    | value 1
  2 | id 2 | key 100  | value 100
  2 | id 2 | key 2    | value 2
  2 | id 2 | key 5150 | value 5150
  2 | id 2 | key 99   | value 99
  2 | id 2 | name     | id 2
(10 rows)


The idea being to eventually run queries like this:

db=> WITH foo AS (
db(>   SELECT id,tags->'name' "name",
db(>          unnest(akeys(tags)) "key",
db(>          unnest(avals(tags)) "val"
db(>     FROM pktest
db(>    WHERE (tags->'name') IS NOT NULL
db(> ) SELECT * FROM foo
db->    WHERE key IN ('key 2', 'key 5150', 'duck')
db->     ORDER BY id,key ;
 id | name |   key    |    val
----+------+----------+------------
  2 | id 2 | key 2    | value 2
  2 | id 2 | key 5150 | value 5150
(2 rows)


Now, two questions:

1. Is there something that guarantees the "key" and "val" columns
   will maintain their expected pairing?

   ... or am i just getting lucky with this simple example?

2. What design considerations sway one's decision to opt for an hstore
   type vs. a separate key-value table for such "tags"?

Thanks for reading/answering,
--patrick