Re: Advice for using integer arrays?

On Tue, Jan 06, 2015 at 12:09:56PM -0500, Michael Heaney wrote:
> I'm fairly new to Postgres, and have a design issue for which an
> array of integers might be a good solution. But I'd like to hear
> from the experts before proceeding down this path.

The biggest consideration is if you are ever intending to use the
values in a join condition. Arrays a efficient space-wise and you also
have good indexing strategies with GIN indexes. You will need to
reframe your queries in terms of ([x] subset-of field) but that's
relatively straightforward.

What doesn't work or is fiddely:

- foreign keys

- selecting part of the list

- reordering or otherwise manipulating the list.

basically, if conceptually the list is a single object which you're
really only going to want to access as a whole, but still want good
indexing, then arrays are for you.

BTW, looking at your example, you might be more interested in ranges,
see for example:
http://www.postgresql.org/docs/9.2/static/rangetypes.html

Conceptually they are a bit different and there isn't support for
multi-ranges AFAIK but they might be more appropriate.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer