From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: [SQL] arrays 
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>,
	Tom Lane <tgl@sss.pgh.pa.us>
Cc: Mike Sosteric <mikes@athabascau.ca>,
	Bruno Wolff III <bruno@wolff.to>, <pgsql-general@postgresql.org>,
	<pgsql-sql@postgresql.org>
Date: Tue, 01 Oct 2002 10:52:53 -0700
Message-ID: <web-1736209@davinci.ethosmedia.com>
In-Reply-To: <Pine.LNX.4.44.0210011037310.661-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

Achilleus,

> I was wondering why is such a rage against arrays.
> 
> I posted 2 very common problems where arrays provide
> the only natural (and efficient) fit. (and got no responses)
> So it seems to me that:

All of your points are correct.   

Us "old database hands" have a knee-jerk reaction against arrays for
long-term data storage because, much of the time, developers use arrays
because they are lazy or don't understand the relational model instead
of because they are the best thing to use.   This is particularly true
of people who come to database development from, say, web design.

In this thread particularly, Mike was suggesting using arrays for a
field used in JOINs, which would be a royal mess.   Which was why you
heard so many arguments against using arrays.

Or, to put it another way:  

1. Array data types are perfect for storing data that arrives in the
form of arrays or matricies, such as scientific data , or interface
programs that store arrays of object properties.     

2. For other purposes, arrays are a very poor substitute for proper
sub-table storage of related data according to the relational model.   

3. The distinguishing factor is "atomicity": ask yourself: "is this
array a discrete and undivisible unit, or is is a collection of related
but mutable elements?" If the former, use and array.  If the latter,
use a sub-table.

Clearer now?

-Josh Berkus