Thread: Asssociative Arrays: Best practices / snippets?

On Saturday 23. May 2009, Stefan Keller wrote:
>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.

The «best practice» here is probably rethinking your design. Storing
an «unforeseeable number of key/value pairs in a column» is a blatant
violation of good database design principles.

Rodrigo posted this link a few hours ago:

http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

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.rembialkowski@gmail.com
http://filip.rembialkowski.net/

On May 23, 2009, at 2:25 AM, Stefan Keller wrote:

> 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?

Take a look at "hstore". It's a data type in contrib that's intended
to store a set of key, value
pairs in a single column.

It's well suited for the case where you're not searching on the keys
and values. If you do
need to search on them then you can either index the hstore field with
a gin or gist index,
or use a separate EAV table for the keys and values.

Cheers,
   Steve