unnesting hstore data

Поиск
Список
Период
Сортировка
От patrick keshishian
Тема unnesting hstore data
Дата
Msg-id CAN0yQBpoX1bwJ5xHV6=dXMUhqBGz3VvFhWd8E84PQYZMiAXQpQ@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Stephen Davies
Дата:
Сообщение: Re: Upgrade from 9.3 to 9.4 issue
Следующее
От: Eelke Klein
Дата:
Сообщение: Re: ftell mismatch with expected position