How to store hash-like objects in a PostgreSQL database?

Question

First, a bit of info. My Rails app has a model called Neo which in turn has attributes observed_at, ra and dec.

However, I now found that I need to store sets of this data (in Python-like fashion it would be a list of tuples.) In Ruby, that would probably be a list of lists.

How can I achieve this? I don't mind using hstore but I would prefer not to as the docs on that are few.

Answer 1

You can use the simpler serialize, instead of hstore.

Alternatively, you can create a model for these sets, then use has_many on Neo, and belongs_to on the new model.

Answer 2

As Guilherme Berger notes, a 1:many relationship is the typical way to model a "list of lists" ... or more precisely, a set of sets; to give it order you have to define some kind of ordering key in each sub-table.

Another option open to you when using PostgreSQL is to use a multi-dimensional array. Unfortunately Pg's arrays are a bit weird, in that a two-dimensional array is more like a matrix (ie: fixed dimensions) rather than a list of lists. If your sub-lists are the same length that's fine, but if they're variable and unbounded lengths arrays of arrays are not a good choice.

I'm mentioning this mostly for completeness's sake, and to explain that serializing language-specific objects in databases is not the right approach to take here. It'll be horrible the first time you have to update to a new major runtime release, change your object structure, or access the database from a different language or a reporting engine. I really don't recommend it.