Best PostgreSQL datatype for storing key-value maps?
I'd like to store a simple map of key-value strings as a field in my PostgreSQL table. I intend to treat the map as a whole; i.e, always select the entire map, and never query by its keys nor values.
I've read articles comparing between hstore, json and jsonb, but those didn't help me choose which data-type is most fitting for my requirements, which are:
- Only key-value, no need for nesting.
- Only strings, no other types nor
null. - Storage efficiency, given my intended use for the field.
- Fast parsing of the queried maps.
What data-type would best fit my use case?
Answer
you could use hstore which is a keymap, however I personally would us jsonb. It's a little overkill, however, most languages can convert json natively without having to decode it yourself.
In json, I'd just store a simple object or array for the info you're trying to store.
Both support indexes and are efficiently stored.
Hstore in text is a custom type format that your language may be unaware of and thus require processing the data to insert or query.