2009/5/23 Stefan Keller <sfkel...@gmail.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.rembialkow...@gmail.com http://filip.rembialkowski.net/
