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@svana.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
