Обсуждение: Arrays
I would like to make a table of 20 plus columns the majority of columns being arrays.
The following test works. The array will hold up to five characteristics of each parameter including the unit of measurement used. Using traditional methods I would need six columns to accomplish the same end (Min, Max, Norm plus a unit column for each).
The downside is that the number of brackets required increases for each succeeding column for insert and update. The last column would comprise 48 brackets, 24 before - 24 after.
Is there a work-around for this.
Bob Pawley
create table specifications (
fluid_id int4 ,
Flow_Rate varchar array[5],
Temperature varchar array[5],
Pressure_In varchar array[5] ,
Pressure_Out varchar array[5]
);
fluid_id int4 ,
Flow_Rate varchar array[5],
Temperature varchar array[5],
Pressure_In varchar array[5] ,
Pressure_Out varchar array[5]
);
insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100, 250, 500, DegF}}',
'{{{10, 40, 100, psi}}}', '{{{{60, 120, 150, psi}}}}' );
'{{{10, 40, 100, psi}}}', '{{{{60, 120, 150, psi}}}}' );
On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: > I would like to make a table of 20 plus columns the > majority of columns being arrays. > > The following test works. The array will hold up to five > characteristics of each parameter including the unit of > measurement used. Using traditional methods I would need six > columns to accomplish the same end (Min, Max, Norm plus a > unit column for each). And why would that be undesirable ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Bob Pawley <rjpawley@shaw.ca> writes:
> insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100, 250, 500, DegF}}',
> '{{{10, 40, 100, psi}}}', '{{{{60, 120, 150, psi}}}}' );
Why are you putting in all those extra braces?
regards, tom lane
Because it gives me an error otherwise.
I am following the rules layed out in the documentation as follows -
Bob
----
8.10.2. Array Value Input
Now we can show some INSERT statements.
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching
dimensionsNote that multidimensional arrays must have matching extents for
each dimension. A mismatch causes an error report.
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
A limitation of the present array implementation is that individual elements
of an array cannot be SQL null values. The entire array can be set to null,
but you can't have an array with some elements null and some not. (This is
likely to change in the future.)
The result of the previous two inserts looks like this:
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} |
{{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} |
{{breakfast,consulting},{meeting,lunch}}
(2 rows)
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 11:16 AM
Subject: Re: [GENERAL] Arrays
> Bob Pawley <rjpawley@shaw.ca> writes:
>> insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100,
>> 250, 500, DegF}}',
>> '{{{10, 40, 100, psi}}}', '{{{{60, 120, 150, psi}}}}' );
>
> Why are you putting in all those extra braces?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
I second that, and I'd love to have someone clarify the appropriate
time to use arrays vs. more columns or an referenced tabled. I've
always found that confusing.
Thanks,
Eric
Karsten Hilbert wrote:
And why would that be undesirable ?
On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote:
<blockquote cite="mid20060126190024.GA5414@merkur.hilbert.loc"
type="cite">
I would like to make a table of 20 plus columns the
majority of columns being arrays.
The following test works. The array will hold up to five
characteristics of each parameter including the unit of
measurement used. Using traditional methods I would need six
columns to accomplish the same end (Min, Max, Norm plus a
unit column for each).
And why would that be undesirable ?
Karsten
I second that, and I'd love to have someone clarify the appropriate time to use arrays vs. more columns or an referenced tabled. I've always found that confusing. Thanks, Eric Karsten Hilbert wrote: > And why would that be undesirable ? > > On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote: > >> I would like to make a table of 20 plus columns the >> majority of columns being arrays. >> >> The following test works. The array will hold up to five >> characteristics of each parameter including the unit of >> measurement used. Using traditional methods I would need six >> columns to accomplish the same end (Min, Max, Norm plus a >> unit column for each). >> > > And why would that be undesirable ? > > Karsten >
On Thu, 26 Jan 2006, Bob Pawley wrote:
> Because it gives me an error otherwise.
What error?
insert into specifications values ('1', '{25, 50, 100, gpm}',
'{100, 250, 500, DegF}',
'{10, 40, 100, psi}', '{60, 120, 150, psi}' );
seems to insert fine for me given the table definition you gave.
> I am following the rules layed out in the documentation as follows -
Are you trying to do multidimensional arrays or just a set of single
dimensional ones?
ERROR: malformed array literal: "{100, 250, 500, DegF)"
I want to do single dimension arrays.
How did I turn it into multidmensional?
Bob
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 11:43 AM
Subject: Re: [GENERAL] Arrays
> On Thu, 26 Jan 2006, Bob Pawley wrote:
>
>> Because it gives me an error otherwise.
>
> What error?
>
> insert into specifications values ('1', '{25, 50, 100, gpm}',
> '{100, 250, 500, DegF}',
> '{10, 40, 100, psi}', '{60, 120, 150, psi}' );
>
> seems to insert fine for me given the table definition you gave.
>
>> I am following the rules layed out in the documentation as follows -
>
> Are you trying to do multidimensional arrays or just a set of single
> dimensional ones?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
ERROR: malformed array literal: "{100, 250, 500, DegF)"
I want to do single dimension arrays.
How did I turn it into multidmensional?
Bob
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 11:43 AM
Subject: Re: [GENERAL] Arrays
> On Thu, 26 Jan 2006, Bob Pawley wrote:
>
>> Because it gives me an error otherwise.
>
> What error?
>
> insert into specifications values ('1', '{25, 50, 100, gpm}',
> '{100, 250, 500, DegF}',
> '{10, 40, 100, psi}', '{60, 120, 150, psi}' );
>
> seems to insert fine for me given the table definition you gave.
>
>> I am following the rules layed out in the documentation as follows -
>
> Are you trying to do multidimensional arrays or just a set of single
> dimensional ones?
Bob Pawley <rjpawley@shaw.ca> writes:
> ERROR: malformed array literal: "{100, 250, 500, DegF)"
You wrote a right paren, not a right brace ...
> I want to do single dimension arrays.
> How did I turn it into multidmensional?
The multiple levels of braces create a multidimensional array.
regards, tom lane
Bob Pawley wrote:
> ERROR: malformed array literal: "{100, 250, 500, DegF)"
Well you have a typo:
"{100, 250, 500, DegF)" is wrong...
"{100, 250, 500, DegF}" is correct...
Sincerely,
Joshua D. Drake
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Thank you - my eyes aren't what they used to be.
Bob
----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Stephan Szabo" <sszabo@megazone.bigpanda.com>; "Tom Lane"
<tgl@sss.pgh.pa.us>; "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 12:20 PM
Subject: Re: [GENERAL] Arrays
> Bob Pawley wrote:
>> ERROR: malformed array literal: "{100, 250, 500, DegF)"
>
> Well you have a typo:
>
> "{100, 250, 500, DegF)" is wrong...
>
> "{100, 250, 500, DegF}" is correct...
>
> Sincerely,
>
> Joshua D. Drake
> --
> The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
I missed that - thanks for the help.
Bob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Stephan Szabo" <sszabo@megazone.bigpanda.com>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 12:12 PM
Subject: Re: [GENERAL] Arrays
> Bob Pawley <rjpawley@shaw.ca> writes:
>> ERROR: malformed array literal: "{100, 250, 500, DegF)"
>
> You wrote a right paren, not a right brace ...
>
>> I want to do single dimension arrays.
>> How did I turn it into multidmensional?
>
> The multiple levels of braces create a multidimensional array.
>
> regards, tom lane
Joshua D. Drake schrieb:
> Bob Pawley wrote:
>
>> ERROR: malformed array literal: "{100, 250, 500, DegF)"
>
>
> Well you have a typo:
>
> "{100, 250, 500, DegF)" is wrong...
>
> "{100, 250, 500, DegF}" is correct...
>
I'd say both are wrong ;)
'{100,250,500,DegF}' could work. But I'm not sure about that
DegF. Since array members are all of the same type - is degf
some integer constant?
Regards
Tino
The order for the array is Min, Norm, Max, Unit.
I'll probably reorder it with the unit first as every value has a unit.
Bob
----- Original Message -----
From: "Tino Wildenhain" <tino@wildenhain.de>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Stephan Szabo"
<sszabo@megazone.bigpanda.com>; "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Thursday, January 26, 2006 12:30 PM
Subject: Re: [GENERAL] Arrays
> Joshua D. Drake schrieb:
>> Bob Pawley wrote:
>>
>>> ERROR: malformed array literal: "{100, 250, 500, DegF)"
>>
>>
>> Well you have a typo:
>>
>> "{100, 250, 500, DegF)" is wrong...
>>
>> "{100, 250, 500, DegF}" is correct...
>>
> I'd say both are wrong ;)
> '{100,250,500,DegF}' could work. But I'm not sure about that
> DegF. Since array members are all of the same type - is degf
> some integer constant?
>
> Regards
> Tino
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Bob Pawley schrieb: > The order for the array is Min, Norm, Max, Unit. > > I'll probably reorder it with the unit first as every value has a unit. > I'd rather create/use a custom datatype for your needs. This array stuff seems overly hackish for me. Regards Tino
Our application will be dispersed amongst many users. I want to keep the datbase as generic as possible. Bob ----- Original Message ----- From: "Tino Wildenhain" <tino@wildenhain.de> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Joshua D. Drake" <jd@commandprompt.com>; "Stephan Szabo" <sszabo@megazone.bigpanda.com>; "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql" <pgsql-general@postgresql.org> Sent: Thursday, January 26, 2006 1:09 PM Subject: Re: [GENERAL] Arrays > Bob Pawley schrieb: >> The order for the array is Min, Norm, Max, Unit. >> >> I'll probably reorder it with the unit first as every value has a unit. >> > > I'd rather create/use a custom datatype for your needs. > This array stuff seems overly hackish for me. > > Regards > Tino > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
I can't imagine
test=# create type stat1 as (i1 int, i2 int, i3 int, t1 text);
CREATE TYPE
test=# create table stest(s1 stat1);
CREATE TABLE
test=# insert into stest values ((1,1,1,'t'));
INSERT 0 1
test=# select * from stest;
s1
-----------
(1,1,1,t)
(1 row)
being a big issue. You've got to create the tables, you can create the
type while you're at it, right?
On Thu, 2006-01-26 at 15:59, Bob Pawley wrote:
> Our application will be dispersed amongst many users.
>
> I want to keep the datbase as generic as possible.
>
> Bob
>
>
> ----- Original Message -----
> From: "Tino Wildenhain" <tino@wildenhain.de>
> To: "Bob Pawley" <rjpawley@shaw.ca>
> Cc: "Joshua D. Drake" <jd@commandprompt.com>; "Stephan Szabo"
> <sszabo@megazone.bigpanda.com>; "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql"
> <pgsql-general@postgresql.org>
> Sent: Thursday, January 26, 2006 1:09 PM
> Subject: Re: [GENERAL] Arrays
>
>
> > Bob Pawley schrieb:
> >> The order for the array is Min, Norm, Max, Unit.
> >>
> >> I'll probably reorder it with the unit first as every value has a unit.
> >>
> >
> > I'd rather create/use a custom datatype for your needs.
> > This array stuff seems overly hackish for me.
> >
> > Regards
> > Tino
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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
On Jan 27, 2006, at 4:41 , Eric E wrote: > I second that, and I'd love to have someone clarify the appropriate > time to use arrays vs. more columns or an referenced tabled. I've > always found that confusing. I would only use arrays if the natural data type of the data is an array, such as some math applications. In these situations, for the most part you are not going to be doing a lot of operations on elements of the array, but rather the array value as a whole. While PostgreSQL does have array support, PostgreSQL is a relational database and as such is designed to handle relational data and is best at handling data that is stored relationally, i.e., in tables and columns. Michael Glaesemann grzm myrealbox com
Thanks Scott - I'll give this a try. Bob ----- Original Message ----- From: "Scott Marlowe" <smarlowe@g2switchworks.com> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Tino Wildenhain" <tino@wildenhain.de>; "Joshua D. Drake" <jd@commandprompt.com>; "Stephan Szabo" <sszabo@megazone.bigpanda.com>; "Tom Lane" <tgl@sss.pgh.pa.us>; "Postgresql" <pgsql-general@postgresql.org> Sent: Thursday, January 26, 2006 2:35 PM Subject: Re: [GENERAL] Arrays >I can't imagine > > test=# create type stat1 as (i1 int, i2 int, i3 int, t1 text); > CREATE TYPE > test=# create table stest(s1 stat1); > CREATE TABLE > test=# insert into stest values ((1,1,1,'t')); > INSERT 0 1 > test=# select * from stest; > s1 > ----------- > (1,1,1,t) > (1 row) > > being a big issue. You've got to create the tables, you can create the > type while you're at it, right? > > On Thu, 2006-01-26 at 15:59, Bob Pawley wrote: >> Our application will be dispersed amongst many users. >> >> I want to keep the datbase as generic as possible. >> >> Bob >> >> >> ----- Original Message ----- >> From: "Tino Wildenhain" <tino@wildenhain.de> >> To: "Bob Pawley" <rjpawley@shaw.ca> >> Cc: "Joshua D. Drake" <jd@commandprompt.com>; "Stephan Szabo" >> <sszabo@megazone.bigpanda.com>; "Tom Lane" <tgl@sss.pgh.pa.us>; >> "Postgresql" >> <pgsql-general@postgresql.org> >> Sent: Thursday, January 26, 2006 1:09 PM >> Subject: Re: [GENERAL] Arrays >> >> >> > Bob Pawley schrieb: >> >> The order for the array is Min, Norm, Max, Unit. >> >> >> >> I'll probably reorder it with the unit first as every value has a >> >> unit. >> >> >> > >> > I'd rather create/use a custom datatype for your needs. >> > This array stuff seems overly hackish for me. >> > >> > Regards >> > Tino >> > >> > ---------------------------(end of >> > broadcast)--------------------------- >> > TIP 2: Don't 'kill -9' the postmaster >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: 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
Bob Pawley schrieb: > Our application will be dispersed amongst many users. > > I want to keep the datbase as generic as possible. > you can "disperse" custom datatypes as well. If this isnt an option, I'd go for a true relational approach with a units table and your main table (value,min,max,unit_id) as real columns. Regards Tino PS: please consider my private message to you regarding e-mail formatting