16

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?

Improve this question
3
  • 2
    Definitely hstore Commented Apr 6, 2017 at 19:08
  • IIRC, jsonb is implemented as a nested hstore, so that should work, too. Commented Apr 6, 2017 at 19:44
  • @a_horse_with_no_name I'd live it if you'd elaborate on that :) Commented Apr 7, 2017 at 15:46

1 Answer 1

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Depending on the programming language hstore does not need any "conversion". The JDBC driver for example simply returns a Map for the contents of a hstore column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.