Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and a good linter?
https://github.com/pjungwir/aggs_for_arrays/
Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy:
http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p...
Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior.
Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
I think I've never used them as column type, but I can imagine a few uses there too.
A lot of cases where you would use a one:many are useful to store in an array instead. Tags would be a good example, multi-select lists, etc.
It's good for some kind of list that you won't search by. That's very limiting, but not an empty set.
Why is that? There are many use cases for data (like vector data) which really needs an array. And it would be unwise to store it as columns. Think of, for example, matrix data or a practically unbounded number of double values coming from a sensor. Plus, PostgreSQL has a limit in the number of columns (1600) of a table, of which you could run out soon if representing this kind of data as regular columns rather than array values.
It seems like you would be trading one fat row for something the database does well (unless you always want all of the sensor data every time)
Everything is a nail and all that...