Postgres Pro
Downloads
Home   >   mailing lists

Thread: Arrays

Arrays

From
Bob Pawley
Date:
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]
 );
 
 insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100, 250, 500, DegF}}',
 '{{{10, 40, 100, psi}}}', '{{{{60, 120, 150, psi}}}}' );
 
 

Re: Arrays

From
Karsten Hilbert
Date:
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

Re: Arrays

From
Tom Lane
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Eric E
Date:
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

Re: Arrays

From
Eric E
Date:
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
>

Re: Arrays

From
Stephan Szabo
Date:
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?

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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?

Re: Arrays

From
Tom Lane
Date:
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

Re: Arrays

From
"Joshua D. Drake"
Date:
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/

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Tino Wildenhain
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Tino Wildenhain
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Scott Marlowe
Date:
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

Re: Arrays

From
Michael Glaesemann
Date:
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

Re: Arrays

From
Bob Pawley
Date:
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

Re: Arrays

From
Tino Wildenhain
Date:
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