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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
