Steve, Filip: Many thanks for your patient answers and concerns. Did'nt know hstore!
=> Is it enough to run the hstore.sql (found in ..\8.3\share\contrib\ plus .dll) in order to install this contrib type under Windows?
=> What's the status of this contribution? Is it going to be still in 8.4? (else I would still consider Arrays).
As for the background: I'm importing data from OpenStreetMap - "the Wikipedia of Maps" - actuall by applying "good" database design principles. This entity-attribute-value model get's mapped to a relational one. Now I won't and can't map all attributes (called tags). That's where the idea about associative arrays came in. The KVPs would be an ANDed in a search with "regular" columns.
So, my answer to Leif's hot-blooded judgment about KVPs could be: "Know when to break the rules" ;->
-S.
2009/5/23 Steve Atkins
<steve@blighty.com>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?
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