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]
);
insert into specifications values ('1', '{25, 50, 100, gpm}', '{{100, 250, 500, DegF}}',
'{{{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
Show quoted text
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?
<!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.<br>
<br>
Thanks,<br>
<br>
Eric<br>
<br>
Karsten Hilbert wrote:<br>
<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>
<br>
</body>
</html>
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:
Show quoted text
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
Show quoted text
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
Show quoted text
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
Show quoted text
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?
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
Show quoted text
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
Show quoted text
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
Show quoted text
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?
Show quoted text
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] ArraysBob 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
Show quoted text
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] ArraysBob 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