PostgreSQL Discussion

Author

Arrays

Bob Pawley

2006-01-26, 4:57 pm

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}}}}' );

Karsten Hilbert

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-01-26, 4:57 pm

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

Bob Pawley

2006-01-26, 4:57 pm

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 | & #123;10000,10000,100
00,10000} |
{& #123;meeting,lunch},
& #123;training,presen
tation}}
Carol | & #123;20000,25000,250
00,25000} |
{& #123;breakfast,consu
lting},& #123;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:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Eric E

2006-01-26, 4:57 pm

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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: 
<blockquote type="cite">
<pre wrap="">And why would that be undesirable ?

On Thu, Jan 26, 2006 at 10:15:22AM -0800, Bob Pawley wrote:
</pre>
</blockquote>
<blockquote cite="mid20060126190024.GA5414@merkur.hilbert.loc"
type="cite">
<blockquote type="cite">
<pre wrap="">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).
</pre>
</blockquote>
<pre wrap="">
And why would that be undesirable ?

Karsten
</pre>
</blockquote>
 
</body>
</html>

Eric E

2006-01-26, 4:57 pm

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:
>
>
> And why would that be undesirable ?
>
> Karsten
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Stephan Szabo

2006-01-26, 4:57 pm

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

Bob Pawley

2006-01-26, 4:57 pm

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:
>
>
> 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.
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Bob Pawley

2006-01-26, 4:57 pm

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:
>
>
> 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.
>
>
> Are you trying to do multidimensional arrays or just a set of single
> dimensional ones?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2006-01-26, 4:57 pm

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

---------------------------(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

Joshua D. Drake

2006-01-26, 4:57 pm

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

Bob Pawley

2006-01-26, 4:57 pm

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:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Bob Pawley

2006-01-26, 4:57 pm

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:
>
> You wrote a right paren, not a right brace ...
>
>
> The multiple levels of braces create a multidimensional array.
>
> regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tino Wildenhain

2006-01-26, 4:57 pm

Joshua D. Drake schrieb:
> Bob Pawley wrote:
>
>
>
> Well you have a typo:
>
> "{100, 250, 500, DegF)" is wrong...
>
> "{100, 250, 500, DegF}" is correct...
>
I'd say both are wrong ;)
'& #123;100,250,500,Deg
F}' 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

2006-01-26, 4:57 pm

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:
> I'd say both are wrong ;)
> '& #123;100,250,500,Deg
F}' 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

---------------------------(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

Tino Wildenhain

2006-01-26, 4:57 pm

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

Bob Pawley

2006-01-26, 4:57 pm

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:
>
> 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

Scott Marlowe

2006-01-26, 4:57 pm

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
>
>
>
>
> ---------------------------(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

---------------------------(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

Michael Glaesemann

2006-01-26, 5:16 pm

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

---------------------------(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

2006-01-26, 8:23 pm

Thanks Scott - I'll give this a try.

Bob

----- Original Message -----
From: "Scott Marlowe" < smarlowe@g2switchwor
ks.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

[color=darkred]
>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:

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tino Wildenhain

2006-01-27, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links