Re: Postgresql 8.3X supports Arrays of Composite Types?

On Sun, Apr 19, 2009 at 5:03 PM, Robson Fidalgo <rdnf(at)cin(dot)ufpe(dot)br> wrote:
> Until here is everything ok, but I have not success with insert values, then
> I tried:
>
> 1)insert into person values ('Joe', '{("1","1111"),("2","2222") }');
> 2)insert into person values ('Joe', array[('1','1111'),('2','2222')]);
> 3)insert into person values ('Joe', array[row('1','1111'),row('2','2222')]);
> 4)insert into person values ('Joe',
> _phone[phone('1','1111'),phone('2','2222')]);**
> ** considering _phone = name of array type (automatically created by
> postgres) and phone = name of composite type (also automatically created by
> postgres)

I agree with David -- arrays of composites should not be used in table
definitions. There are exceptions, but you have to be very cautious.
The phone number composite is basically ok, but I'd advise dropping
the array minimum. Here are the basic problems:

*) constraint checking vs. array of composites is problematic
*) updating a specific field of a specific composite is not really
possible...you have to build a complete new composite array and update
the table with it.
*) searching (who has a phone number x?) is a problem

Imagine a client changes one of his/her phone numbers and compare the
sql you would have to write doing it the classic way vs. your way.

As David noted, if you like the composite format in the presentation
of data, you can trivially do this in view. There may be reasons to
do this -- the advantages of composite are convenience in passing data
to/from functions and nesting data returned to the client. There is
no disadvantage of nesting data 'in query' -- that's how I do it and
it works very well.

There are a couple of exceptions to the 'no arrays in table'. You may
have a lot of static data (think float[]) that you are doing numerical
analysis on the client for example. It only ever gets
inserted/selected/deleted in bulk and never updated. There are other
exceptions, but they are rare. Usually it's better doing it the 'sql
way'

merlin