Thread: nOOB Question..
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
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
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
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)
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
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
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