From: | Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> |
---|---|
To: | Stefan Keller <sfkeller(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Asssociative Arrays: Best practices / snippets? |
Date: | 2009-05-23 11:24:28 |
Message-ID: | 92869e660905230424x500d5060p4fd72b1cc96041e2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2009/5/23 Stefan Keller <sfkeller(at)gmail(dot)com>
> I have a use case where the I want to put an unforeseable number of
> key/value pairs in a column.
> Now, PostgreSQL has arrays as first class types.
> Are there any best practices and snippets (preferrably in plpgsql) for
> handling key/value pairs?
> -- S.
>
common relational practice is to use entity-attribute-value model to attach
any number of key/value pairs to any object, for example:
object( object_id, ... )
attribute ( attr_id, attr_name )
object_attribute ( object_id, attr_id, attr_value )
if your a lazy DBA and text key/values are all you need, did you look at
hstore? it might be enough.
my personal hstore experience is:
1) keep your key/values reasonably small; if you need structured data - do
not put it into hstore.
2) keep in mind that hstore indexing is limited, many search operations
require full table scan
--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Havasvölgyi Ottó | 2009-05-23 12:01:02 | 8.3: timestamp subtraction |
Previous Message | Leif B. Kristensen | 2009-05-23 10:22:24 | Re: Asssociative Arrays: Best practices / snippets? |