Thread: nOOB Question..

nOOB Question..

From
Herbie
Date:
Greetings All!!

I've been using PosgreSQL for less than one week. Got it installed and it
appears to be running.

Before really screwing things up, I like to at least read the docs at least
once.

Being a business/accounting type developer, I noticed that we have access to
array type columns.

In the example I found:

  CREATE TABLE foo (
      barr                 text,
      doo_daa       numeric(7,2)[]

);

Is this the correct syntax for create a the row doo_daa as a numeric array?

If this is correct, what is the limit on the number of the extents of the array?

--Herbie

Re: nOOB Question..

From
Josh Berkus
Date:
Herbie,

> I've been using PosgreSQL for less than one week. Got it installed and it
> appears to be running.

Congratulations.

> Before really screwing things up, I like to at least read the docs at least
> once.

<grin>
BTW, there are also several good PostgreSQL books on the market which are more
accessable than the online docs.

> Being a business/accounting type developer, I noticed that we have access
> to array type columns.
>
> In the example I found:
>
>   CREATE TABLE foo (
>       barr                 text,
>       doo_daa       numeric(7,2)[]
>
> );
>
> Is this the correct syntax for create a the row doo_daa as a numeric array?

I believe so, yes.

> If this is correct, what is the limit on the number of the extents of the
> array?

There isn't one.  You can provide one (NUMERIC(7,2)[5]) but it will not be
enforced.   Joe Conway is overhauling array handling for 7.4; I don't know if
array limits will be enforcable in the future.

FWIW, I primarily develop business/accounting applications, and have to say
that arrays as a data type have no place in business/financial applications
in permanent tables.  I have yet to see a case in such an application where a
child table was not more appropriate than an array.  Arrays are very useful
for functions, caching, and temporary tables for "flattened" relational data,
but should be avoided for real data tables.

(Please note that this does not apply to scientific, mathematical, or
geographic databases, which have *lots* of reasons to use arrays)


--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: nOOB Question..

From
Dani Oderbolz
Date:
Josh Berkus wrote:

>FWIW, I primarily develop business/accounting applications, and have to say
>that arrays as a data type have no place in business/financial applications
>in permanent tables.  I have yet to see a case in such an application where a
>child table was not more appropriate than an array.
>
Hi,
I completely agree with Josh.
Its much easier to query another table then to fiddle the data out of
the Array.
For me, arrays make sense only in some special cases, in all others
its like a violation of the relational paradigma.

Cheers, Dani



Re: nOOB Question..

From
János Löbb
Date:
In FoxPro I used to put the data presented on a form into two arrays.
One contained the "before modification" the other the "after
modification" data.  After hitting an <OK> button I could quickly check
if the user made any modification just by comparing the values in the
two array and update the values in the table accordingly by creating
the appropriate SQL statement dynamically on the fly.  It was much
quicker than messing with any temp tables or cursors alike.  When you
write data entry form applications speed is everything.  You do not
want to slow down the very fast data entry folks who type without
looking the keyboard and stop and see the form only if an audible
feedback alerts them for something wrong.
So that is one case when arrays are very useful.
János

On Monday, Jun 2, 2003, at 05:56 America/New_York, Dani Oderbolz wrote:

> Josh Berkus wrote:
>
>> FWIW, I primarily develop business/accounting applications, and have
>> to say that arrays as a data type have no place in business/financial
>> applications in permanent tables.  I have yet to see a case in such
>> an application where a child table was not more appropriate than an
>> array.
> Hi,
> I completely agree with Josh.
> Its much easier to query another table then to fiddle the data out of
> the Array.
> For me, arrays make sense only in some special cases, in all others
> its like a violation of the relational paradigma.
>
> Cheers, Dani
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
----------------------------------------------
Trying to argue with a politician is like lifting up the head of a
corpse.
(S. Lem: His Master Voice)


Re: nOOB Question..

From
Herbie McDuck
Date:
On Monday 02 June 2003 05:56, Dani Oderbolz wrote:
> Josh Berkus wrote:
> >FWIW, I primarily develop business/accounting applications, and
> > have to say that arrays as a data type have no place in
> > business/financial applications in permanent tables.  I have yet
> > to see a case in such an application where a child table was not
> > more appropriate than an array.
>
> Hi,
> I completely agree with Josh.
> Its much easier to query another table then to fiddle the data out
> of the Array.
> For me, arrays make sense only in some special cases, in all others
> its like a violation of the relational paradigma.

Well bro, I don't really know who is completely right other than go
with the Josh POV because of his experience.

But I can see the validity of both points of view on the matter.

Again, still developing with a limited set of data and less than zero
experience, what is the overhead cost in processing response when one
uses another table vs doing the bit fiddle with a table array?

I presume when the use of foreign keys and logical indexes are used to
link an external table for the array that the ease and clarity of
coding would outweigh the potential gain in processing speed derived
with maintaining the array within the host table.

YMMV...

--Herbie


Re: nOOB Question..

From
Josh Berkus
Date:
Janos,

> In FoxPro I used to put the data presented on a form into two arrays.
> One contained the "before modification" the other the "after
> modification" data.  After hitting an <OK> button I could quickly check
> if the user made any modification just by comparing the values in the
> two array and update the values in the table accordingly by creating
> the appropriate SQL statement dynamically on the fly.  It was much
> quicker than messing with any temp tables or cursors alike.  When you
> write data entry form applications speed is everything.  You do not
> want to slow down the very fast data entry folks who type without
> looking the keyboard and stop and see the form only if an audible
> feedback alerts them for something wrong.

Yes, absolutely.   This is what I meant about how arrays can be very useful
for loading/unloading data from more complex schema.   You just don't want
the array to be your permanent storage format.

Unless, as I said before, you're dealing with actual array data, such as math
and scientific databases.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: nOOB Question..

From
Josh Berkus
Date:
Herbie,

> Well bro, I don't really know who is completely right other than go
> with the Josh POV because of his experience.

Hey, it's not just my opinion.  It's Relational Database Theory.  Check out
Fabian Pascal's "Practical Issues in Database Design"  or C.J. Date's "Intro
to Database Systems".

The idea is that your tables should be a normalized reflection of the actual
data.  If your *actual data* is an array (such as a mathmatical matrix) then
definitely store it as an array.  If it's child data (such as a list of sales
per item), then store it as a child table.

Taking shortcuts to "improve performance" when you don't even know that you
have a performance problem is foolish.

--
Josh Berkus
Aglio Database Solutions
San Francisco