Thread: Re: [SQL] arrays

Mike Sosteric <mikes@athabascau.ca> writes:
> could you select where title[0] = 'en'

You certainly could ... but bear in mind that there's no convenient way
to make such a query be indexed, at present.  So any values that you
actually want to use as search keys had better be in their own fields.

Now, if you are just using this as an extra search condition that picks
one row out of a small number that are identified by another WHERE
clause, then it's good enough to index for the other clause, and so the
lack of an index for title[0] isn't an issue.  In this case, with only
a small number of possible values for title[0], it seems that an index
wouldn't be helpful anyway.

            regards, tom lane

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:

- Arrays implementation (along with the intarray package) in postgresql
  is well performing and stable.
- Some problems shout out for array usage.
- The Array interface is defined in java.sql package.
  (I dont know if sql arrays is in some standard but it seems that
  Java sees it that way, at least).
- The Array interface is implemented in the official postgresql java
package.
- In some problems replacing arrays according the tradition relational
paradigm would end up in a such a performance degradation, that
some applications would be unusable.
- Oleg and Teodor did a great job in intarray, making array usage
 easy and efficient.

Thanx!


==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr

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