From: | patrick keshishian <pkeshish(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | unnesting hstore data |
Date: | 2015-11-01 03:22:34 |
Message-ID: | CAN0yQBpoX1bwJ5xHV6=dXMUhqBGz3VvFhWd8E84PQYZMiAXQpQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Eelke Klein | 2015-11-02 09:48:50 | Re: ftell mismatch with expected position |
Previous Message | Stephen Davies | 2015-11-01 00:28:36 | Re: Upgrade from 9.3 to 9.4 issue |