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.

The simplest way would be in a table of four columns--three for "observed_at", "ra", and "dec", and a fourth column to store an identifier for the set. What data types are you using for those columns? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Apr 20, 2013 at 12:17
observed_at is obviously a date. ra and dec are both floats. Howver, each Neo has multiple sets of these. — Soham Chowdhury
– Soham Chowdhury, Commented Apr 20, 2013 at 12:34

gberger · Accepted Answer · 2013-04-20 12:37:14Z

6

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.

answered Apr 20, 2013 at 12:37

gberger

2,9033 gold badges32 silver badges52 bronze badges

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

2 Comments

Soham Chowdhury Over a year ago

The second option hit me right now. Thanks for the help anyway.

Craig Ringer Over a year ago

I'm not a big fan of serialized language-specific objects in databases. They're a major pain when you upgrade the language runtime or change the object structure, and they're immensely frustrating when you need to talk to the database with another language, a reporting engine, etc. A simple 1:m relationship is a much saner approach.

Craig Ringer · Accepted Answer · 2013-04-21 05:14:24Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related