choosing between arrays, json and hstore datatypes in postgres and rails

Question

I have some fields that are related to other fields and I think to put it in one database column will be much cleaner.

For example lets say I have these fields: parking_available:boolean paid_parking:boolean free_parking:boolean parking_price:integer and reservation_required:boolean

So if I use arrays,json or hstore then I may store and fetch the values for above parameters in order as strings without considering the datatypes. But I am confused on which one is better here or stick with the individual database fields. Could someone explain the use cases for arrays,json and hstore and performace benefits of these?

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Although, it directly does not answer your question, How to persist hashes in rails application with postgresql stated about hstore, JSON and JSONB

Although it may not be a common case to store other types than string in a dictionary, it is still worth to have in mind that hstore supports only string data.

...

it (JSON) supports nested objects and more datatypes. It also has more operators that are very well described in documentation. If you are using JSON somewhere in your application already and want to store it directly in database, then the JSON datatype is perfect choice.

...

If you still wonder whether MongoDB will be better choice for you needs, check JSONB support introduced in 9.4 version of postgres, which is real data type with binary storage and indexing, a structured format for storing json

According to my interpretation of the above source, you should use JSON or JSONB (if you already using PostgreSQL 9.4+ or no inhibiting factor to change to 9.4+)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 22, 2016 at 15:37

Tun

1,45717 silver badges37 bronze badges

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

2 Comments

Abhilash Over a year ago

doesnt that article recommends jsonb over json??

Tun Over a year ago

Yes, it seems so. Personally to me, I haven't study about the JSONB in PostgreSQL. And so my mind come up with JSON to recommend to you. My bad.

Collectives™ on Stack Overflow

choosing between arrays, json and hstore datatypes in postgres and rails

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related