[jaggederest]

Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.

[joevandyk]

While I probably wouldn't store things in an array, it's useful to get data back out in array format sometimes.

    select array_agg(email_address), home_state from users group by home_state

Will give you a list of home states and all the email addresses that belong to that state.

[radiowave]

Right. A case I've used them for is a recursive query which returns a set of rows, where each row is some end-point that matches the query criteria, the rows can include arrays representing the list of nodes traversed to reach that end-point, or some notion of the path cost by hop.

[einhverfr]

Foreign keys as arrays is just wrong.

However, there are many cases where arrays are actually extremely useful, including the ability to be a useful intermediary type for stored procedure interfaces. For example, you can aggregate arrays of rows, and pass those into another stored procedure for processing, or you can use them for pulling info to/from your application. We do this extensively in PostgreSQL in part because DBD::Pg has excellent array support.

These are actually remarkably useful in PostgreSQL. Of course like any advanced feature it can be abused.