unnesting hstore data

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

Browse pgsql-general by date

  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