pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedarrays
36+ messages / 20 participants
[nested] [flat]
* arrays
@ 1998-09-22 23:40 Brook Milligan <brook@trillium.NMSU.Edu>
0 siblings, 0 replies; 36+ messages in thread
From: Brook Milligan @ 1998-09-22 23:40 UTC (permalink / raw)
To: pgsql-sql
Is it possible to add an element (or more than one element) to an
array in an UPDATE?
For example, suppose I have a field like
a int[]
and it contains the following
{ 1, 2, 3 }
Can I UPDATE this by adding 4 to the end so that it is now
{ 1, 2, 3, 4 }
Cheers,
Brook
^ permalink raw reply [nested|flat] 36+ messages in thread
* arrays
@ 1999-12-01 22:34 Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
0 siblings, 2 replies; 36+ messages in thread
From: Marcin Mazurek - Multinet SA - Poznan @ 1999-12-01 22:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table: orders (id int, val INT4[][]);. The question is how to
select in separate rows values from particular id?
example:
insert into orders values(1,'{{1,1},{1,4},{2,3}}');
select val from orders where id=1; gives me:
val
-------------------
{{1,2},{1,4},{2,3}}
How can I make from it to columned table with first and second int
separated?
mazek
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-02 10:20 Karel Zak - Zakkr <zakkr@zf.jcu.cz>
parent: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
1 sibling, 1 reply; 36+ messages in thread
From: Karel Zak - Zakkr @ 1999-12-02 10:20 UTC (permalink / raw)
To: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>; +Cc: pgsql-sql
On Wed, 1 Dec 1999, Marcin Mazurek - Multinet SA - Poznan wrote:
> Hi,
> I have a table: orders (id int, val INT4[][]);. The question is how to
> select in separate rows values from particular id?
>
> example:
> insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> select val from orders where id=1; gives me:
> val
> -------------------
> {{1,2},{1,4},{2,3}}
>
If I good undertend you.. You want select values from a array in separate
rows. Hmm see:
select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
from orders o where id=1;
val
---
1
2
(2 rows)
And you have selected first dimension of the array now. But it is terrible
method if you have a lot of dimensions and if you want search in array
(for this is '*=' operator ..etc.).
Karel
----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Docs: http://docs.linux.cz (big docs archive)
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
-----------------------------------------------------------------------
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-02 11:01 Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
parent: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 36+ messages in thread
From: Marcin Mazurek - Multinet SA - Poznan @ 1999-12-02 11:01 UTC (permalink / raw)
To: Karel Zak - Zakkr <zakkr@zf.jcu.cz>; +Cc: pgsql-sql
On Thu, 2 Dec 1999, Karel Zak - Zakkr wrote:
> > I have a table: orders (id int, val INT4[][]);. The question is how to
> > select in separate rows values from particular id?
> > example:
> > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > select val from orders where id=1; gives me:
> > val
> > -------------------
> > {{1,2},{1,4},{2,3}}
> If I good undertend you.. You want select values from a array in separate
> rows. Hmm see:
> select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
> from orders o where id=1;
> val
> ---
> 1
> 2
> (2 rows)
what i meant was:
c1 c2
1 2
1 4
2 3
how can i do this?
mazek
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-02 11:11 Karel Zak - Zakkr <zakkr@zf.jcu.cz>
parent: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
0 siblings, 0 replies; 36+ messages in thread
From: Karel Zak - Zakkr @ 1999-12-02 11:11 UTC (permalink / raw)
To: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>; +Cc: pgsql-sql
On Thu, 2 Dec 1999, Marcin Mazurek - Multinet SA - Poznan wrote:
> On Thu, 2 Dec 1999, Karel Zak - Zakkr wrote:
> > > I have a table: orders (id int, val INT4[][]);. The question is how to
> > > select in separate rows values from particular id?
> > > example:
> > > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > > select val from orders where id=1; gives me:
> > > val
> > > -------------------
> > > {{1,2},{1,4},{2,3}}
> > If I good undertend you.. You want select values from a array in separate
> > rows. Hmm see:
> > select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
> > from orders o where id=1;
> > val
> > ---
> > 1
> > 2
> > (2 rows)
> what i meant was:
> c1 c2
> 1 2
> 1 4
> 2 3
> how can i do this?
> mazek
>
Oh man, it is total equivalent:
select o.val[1][1] as c1, o.val[1][2] as c2 from orders o where id=1
UNION ALL
select o.val[2][1], o.val[2][2] from orders o where id=1
UNION ALL
select o.val[3][1], o.val[3][2] from orders o where id=1;
c1|c2
--+--
1| 2
1| 4
2| 3
(3 rows)
Karel
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 02:23 Peter Eisentraut <peter_e@gmx.net>
parent: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
1 sibling, 1 reply; 36+ messages in thread
From: Peter Eisentraut @ 1999-12-04 02:23 UTC (permalink / raw)
To: Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>; +Cc: pgsql-sql
Don't use arrays. They look very promising but they aren't. Most of these
kind of questions can be resolved by designing a proper relational
database schema.
On 1999-12-01, Marcin Mazurek - Multinet SA - Poznan mentioned:
> Hi,
> I have a table: orders (id int, val INT4[][]);. The question is how to
> select in separate rows values from particular id?
>
> example:
> insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> select val from orders where id=1; gives me:
> val
> -------------------
> {{1,2},{1,4},{2,3}}
>
> How can I make from it to columned table with first and second int
> separated?
>
> mazek
>
>
>
> ************
>
>
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 09:31 Stephen Davies <scldad@sdc.com.au>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 36+ messages in thread
From: Stephen Davies @ 1999-12-04 09:31 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql@postgreSQL.org, Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
Arrays do have a role in life: even in a "proper relational" world.
The classical examples are publications with multiple authors, ISBns,
subjects, keywords etc.
Trying to fully normalise attributes like these leads to nonsensical and
unusable database designs.
Cheers,
Stephen.
Peter Eisentraut <peter_e@gmx.net> wrote:
> Don't use arrays. They look very promising but they aren't. Most of these
> kind of questions can be resolved by designing a proper relational
> database schema.
>
> On 1999-12-01, Marcin Mazurek - Multinet SA - Poznan mentioned:
>
> > Hi,
> > I have a table: orders (id int, val INT4[][]);. The question is how to
> > select in separate rows values from particular id?
> >
> > example:
> > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > select val from orders where id=1; gives me:
> > val
> > -------------------
> > {{1,2},{1,4},{2,3}}
> >
> > How can I make from it to columned table with first and second int
> > separated?
> >
> > mazek
> >
> >
> >
> > ************
> >
> >
>
> --
> Peter Eisentraut Sernanders väg 10:115
> peter_e@gmx.net 75262 Uppsala
> http://yi.org/peter-e/ Sweden
>
>
>
> ************
--
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 14:02 John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
parent: Stephen Davies <scldad@sdc.com.au>
0 siblings, 2 replies; 36+ messages in thread
From: John M. Flinchbaugh @ 1999-12-04 14:02 UTC (permalink / raw)
To: pgsql-sql
On Sat, 4 Dec 1999, Stephen Davies wrote:
> Arrays do have a role in life: even in a "proper relational" world.
> The classical examples are publications with multiple authors, ISBns,
> subjects, keywords etc.
> Trying to fully normalise attributes like these leads to nonsensical and
> unusable database designs.
sorry, that is incorrect. in a proper system, you don't run out of
``slots''. say you give each publication an array to hold 3
authors. what happens when that 4th author comes along, then a
fifth? you always have a +1 problem. your database design will stand the
test of time better if you keep a good design.
one may say, ``just put 100 slots for authors''. then you are just
wasting space. if you normalize it (in this case store a table containing
the association between a publication and an author), you never hit an
upper limit, and you don't waste space in all those publication tables.
____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <- john@cs.millersv.edu |
| glynis@netrax.net http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 15:05 neko@kredit.sth.szif.hu
parent: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
1 sibling, 0 replies; 36+ messages in thread
From: neko@kredit.sth.szif.hu @ 1999-12-04 15:05 UTC (permalink / raw)
To: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>; +Cc: pgsql-sql
On Sat, 4 Dec 1999, John M. Flinchbaugh wrote:
> > Trying to fully normalise attributes like these leads to nonsensical and
> > unusable database designs.
> ``slots''. say you give each publication an array to hold 3
> authors. what happens when that 4th author comes along, then a
> fifth? you always have a +1 problem. your database design will stand the
> test of time better if you keep a good design.
>
> one may say, ``just put 100 slots for authors''. then you are just
Hmm... But you can create an array field with variable size. So, it's not
waste the space, and have only one limit: 8k record size.
So, feel free to use arrays.
best regards:
--
nek;(
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 17:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
1 sibling, 1 reply; 36+ messages in thread
From: Tom Lane @ 1999-12-04 17:41 UTC (permalink / raw)
To: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>; +Cc: pgsql-sql
"John M. Flinchbaugh" <glynis@butterfly.hjsoft.com> writes:
> On Sat, 4 Dec 1999, Stephen Davies wrote:
>> Arrays do have a role in life: even in a "proper relational" world.
> sorry, that is incorrect. in a proper system, you don't run out of
> ``slots''. say you give each publication an array to hold 3
> authors. what happens when that 4th author comes along, then a
> fifth? you always have a +1 problem.
Actually, Postgres arrays are variable-size, so that argument loses much
of its force. AFAICT, even if you specify a particular size in the
table declaration, Postgres ignores it and makes each tuple's array just
big enough to hold whatever gets stored there.
I have used arrays in some cases where they seemed to be just the right
thing and made the database design much cleaner than it would have been
otherwise. For example, in a futures-trading transaction database,
I made the fill price and contract count be arrays, so that I could
represent "split fill" orders (you to broker: "Buy 10 June S&P futures";
broker to you: "OK, I got 6 at 1415.5 and 4 at 1415.6"). Normalizing
this problem into separate transaction and price tables with an
association table is obviously ridiculous; nor did I like the choice of
representing a split fill as two or more separate transaction entries.
That wastes space for duplicate storage of the other columns, and also
means that you have to work harder to group together several database
records as a single "logical trade". One record per trade with the
ability to store multiple fill prices is the right design here.
Arrays have lots of problems though. For one, there's no clean way to
do something like "find all the records that have FOO in any element of
this array column". We could invent some operators that do that kind of
thing, but they're not there now. Also, you can't expect to put a
really large number of items in an array, because of the system's limit
on overall tuple length; but that limit will go away someday (fairly
soon I think).
There is some ancient code in the backend for storing big arrays
out-of-line as Large Objects, which'd eliminate the tuple-length issue.
But I think that code isn't getting compiled now, much less tested
or used regularly. So it's most likely broken.
The real problem with arrays in Postgres is that they need a lot of
work put into them, and no one has stepped up to the plate to do it.
Fixing the LOARRAY code, allowing NULL array elements, adding functions
to do searches within arrays, etc, etc, are all things that could and
should get done, but they're not high on the priority list of any active
developer. Arrays need to get adopted by somebody.
regards, tom lane
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 1999-12-04 19:45 kaiq@realtyideas.com
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 36+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-04 19:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>; pgsql-sql
seems to me it is another example of sql86/92 issue: there is
no standard regard to those object-relational feature.
Informix is our relative and of course the best (and
we are going to be better :-), but Orocle's array has maxim length
and unfortunately they are the most powerful. and
everybody, at least ambitious ones, have a snob inside (and of
course have jargon excuses for that (I'm :-). That is the reason
to be careful/cautious about any non-sql86/92 feature and this
attitude is good for all oss.
Actually, I guess, that is at least one of the reason why
it is not at the top priority: let's make the sql2 first, sql3,
if she will come, can wait.
On Sat, 4 Dec 1999, Tom Lane wrote:
> "John M. Flinchbaugh" <glynis@butterfly.hjsoft.com> writes:
> > On Sat, 4 Dec 1999, Stephen Davies wrote:
> >> Arrays do have a role in life: even in a "proper relational" world.
>
> > sorry, that is incorrect. in a proper system, you don't run out of
> > ``slots''. say you give each publication an array to hold 3
> > authors. what happens when that 4th author comes along, then a
> > fifth? you always have a +1 problem.
>
> Actually, Postgres arrays are variable-size, so that argument loses much
> of its force. AFAICT, even if you specify a particular size in the
> table declaration, Postgres ignores it and makes each tuple's array just
> big enough to hold whatever gets stored there.
>
> I have used arrays in some cases where they seemed to be just the right
> thing and made the database design much cleaner than it would have been
> otherwise. For example, in a futures-trading transaction database,
> I made the fill price and contract count be arrays, so that I could
> represent "split fill" orders (you to broker: "Buy 10 June S&P futures";
> broker to you: "OK, I got 6 at 1415.5 and 4 at 1415.6"). Normalizing
> this problem into separate transaction and price tables with an
> association table is obviously ridiculous; nor did I like the choice of
> representing a split fill as two or more separate transaction entries.
> That wastes space for duplicate storage of the other columns, and also
> means that you have to work harder to group together several database
> records as a single "logical trade". One record per trade with the
> ability to store multiple fill prices is the right design here.
>
> Arrays have lots of problems though. For one, there's no clean way to
> do something like "find all the records that have FOO in any element of
> this array column". We could invent some operators that do that kind of
> thing, but they're not there now. Also, you can't expect to put a
> really large number of items in an array, because of the system's limit
> on overall tuple length; but that limit will go away someday (fairly
> soon I think).
>
> There is some ancient code in the backend for storing big arrays
> out-of-line as Large Objects, which'd eliminate the tuple-length issue.
> But I think that code isn't getting compiled now, much less tested
> or used regularly. So it's most likely broken.
>
> The real problem with arrays in Postgres is that they need a lot of
> work put into them, and no one has stepped up to the plate to do it.
> Fixing the LOARRAY code, allowing NULL array elements, adding functions
> to do searches within arrays, etc, etc, are all things that could and
> should get done, but they're not high on the priority list of any active
> developer. Arrays need to get adopted by somebody.
>
> regards, tom lane
>
> ************
>
^ permalink raw reply [nested|flat] 36+ messages in thread
* Arrays
@ 2000-12-31 21:29 Albert REINER <areiner@tph.tuwien.ac.at>
0 siblings, 1 reply; 36+ messages in thread
From: Albert REINER @ 2000-12-31 21:29 UTC (permalink / raw)
To: pgsql-sql
Saluton,
I somehow get the impressions that support for arrays is not a high
priority with PostgreSQL, even though I think it would be very
handy. On the other hand, the system itself uses arrays e.g. for user
groups, so I guess there should be readily available
functions/operators for dealing with that, but I cannot seem to be
able to find them.
Is there a simple way of checking whether e.g. a given int4 is in a
given int4-array?
Is there a way to "thread" a command over an array? By this I mean:
Given an array {1, 2, 3} and a table t like
id | name
--------|--------
1 | joe
2 | fred
3 | mark
, a call to select thread('||', t, {1,2,3}) should produce
'joefredmark'.
Thanks in advance,
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Arrays
@ 2000-12-31 22:28 Oleg Bartunov <oleg@sai.msu.su>
parent: Albert REINER <areiner@tph.tuwien.ac.at>
0 siblings, 0 replies; 36+ messages in thread
From: Oleg Bartunov @ 2000-12-31 22:28 UTC (permalink / raw)
To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql
Wait some time. We have something you need - we implement RD-tree
using GiST interface. Unfortunately, GiST was very poorly tested
and supported, so we have to understand, fix and rewrite some part of code.
There are still some problems but we hope to get working code in January.
Oleg
On Sun, 31 Dec 2000, Albert REINER wrote:
> Date: Sun, 31 Dec 2000 22:29:33 +0100
> From: Albert REINER <areiner@tph.tuwien.ac.at>
> To: PostgreSQL-SQL <pgsql-sql@postgresql.org>
> Subject: [SQL] Arrays
>
> Saluton,
>
> I somehow get the impressions that support for arrays is not a high
> priority with PostgreSQL, even though I think it would be very
> handy. On the other hand, the system itself uses arrays e.g. for user
> groups, so I guess there should be readily available
> functions/operators for dealing with that, but I cannot seem to be
> able to find them.
>
> Is there a simple way of checking whether e.g. a given int4 is in a
> given int4-array?
>
> Is there a way to "thread" a command over an array? By this I mean:
> Given an array {1, 2, 3} and a table t like
>
> id | name
> --------|--------
> 1 | joe
> 2 | fred
> 3 | mark
>
> , a call to select thread('||', t, {1,2,3}) should produce
> 'joefredmark'.
>
> Thanks in advance,
>
> Albert.
>
>
> --
>
> --------------------------------------------------------------------------
> Albert Reiner <areiner@tph.tuwien.ac.at>
> Deutsch * English * Esperanto * Latine
> --------------------------------------------------------------------------
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 36+ messages in thread
* Arrays
@ 2001-01-11 18:06 Sharmad Naik <sharmad@im.eth.net>
0 siblings, 0 replies; 36+ messages in thread
From: Sharmad Naik @ 2001-01-11 18:06 UTC (permalink / raw)
To: pgsql-sql; +Cc: linux-india-help@lists.linux-india.org; linux-india-programmers@lists.linux-india.org
hi ,
I m a newbi to postgres. I wantd to know whether i can implement arrays as primary key
I had problems changing all to a corrresponding ID as the usual syntax wouldn't allow us to do so (might be i m wrong)
Please help me
TIA
Sharmad
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 00:12 Mike Sosteric <mikes@athabascau.ca>
0 siblings, 4 replies; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 00:12 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-sql
On Sun, 29 Sep 2002, Bruce Momjian wrote:
Apologies in advance if there is a more appropriate list.
We are currently developing a database to host some complicated, XMl
layered data. We have chosen postgres because of its ability to store
multidimensional arrays. We feel that using these will allow us to
simplify the database structure considerably by storing some data in
multidimensional arrays.
However, we currently have some dissenters who believe that using the
multidimensional arrays will make queries slower and unneccesarily
complicated. Its hard for us to evaluate in advance because none of us
have much experience with postgres (we are web based and have relied on
MySQL for most projects up to this point).
I have several questions related to the scenario above.
1) are SQL queries slower when extracting data from multidimensional
arrays
2) are table joins more difficult or unneccesarily complicated
3) can you do selects on only a portion of a multidimensional array. That
is, if you were storing multilanguage titles in a two dimensional array,
[en], "english title"
[fr], "french title"
could you select where title[0] = 'en'
I know these may sound like terribily stupid questions. but we need some
quick guidance before proceeding with a schema that relies on these
advanced data features of postgres
tia
mike
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 12:29 Bruno Wolff III <bruno@wolff.to>
parent: Mike Sosteric <mikes@athabascau.ca>
3 siblings, 2 replies; 36+ messages in thread
From: Bruno Wolff III @ 2002-09-30 12:29 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Sun, Sep 29, 2002 at 18:12:55 -0600,
Mike Sosteric <mikes@athabascau.ca> wrote:
> On Sun, 29 Sep 2002, Bruce Momjian wrote:
>
> 3) can you do selects on only a portion of a multidimensional array. That
> is, if you were storing multilanguage titles in a two dimensional array,
>
> [en], "english title"
> [fr], "french title"
>
> could you select where title[0] = 'en'
It is unusual to want to store arrays in a database. Normally you want to
use additional tables instead. For example multilanguage titles is something
I would expect to be in a table that had a column referencing back to
another table defining the object a title was for, a column with the
title and a column with the language.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 12:38 Mike Sosteric <mikes@athabascau.ca>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 2 replies; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 12:38 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Mon, 30 Sep 2002, Bruno Wolff III wrote:
> > 3) can you do selects on only a portion of a multidimensional array. That
> > is, if you were storing multilanguage titles in a two dimensional array,
> >
> > [en], "english title"
> > [fr], "french title"
> >
> > could you select where title[0] = 'en'
>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.
>
The chances are very very good that in 99% of the cases we'd only ever
have a single title. multiple titles would be rare. and, to make it worse,
there are several instances of this where you need a table but its seems
overkill for the odd 1% time when you actually need teh extra row.
of course, the there'd be a language lookup table.
what about the speed and query issue?
m
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 13:18 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 36+ messages in thread
From: Achilleus Mantzios @ 2002-09-30 13:18 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Mike Sosteric <mikes@athabascau.ca>; pgsql-general@postgresql.org; pgsql-sql
On Mon, 30 Sep 2002, Bruno Wolff III wrote:
>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.
I think arrays are one of the cool features of postgres
(along with gist indexes).
Here are some common uses:
- Tree representation (the genealogical from child to ancestors approach)
- Storing of polynomial formulae of arbitary degree
checkout the intarray package in contrib for further info.
I think pgsql arrays provide a natural solution to certain problems
where it fits.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 13:57 Bruno Wolff III <bruno@wolff.to>
parent: Mike Sosteric <mikes@athabascau.ca>
1 sibling, 0 replies; 36+ messages in thread
From: Bruno Wolff III @ 2002-09-30 13:57 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Mon, Sep 30, 2002 at 06:38:56 -0600,
Mike Sosteric <mikes@athabascau.ca> wrote:
> On Mon, 30 Sep 2002, Bruno Wolff III wrote:
>
> The chances are very very good that in 99% of the cases we'd only ever
> have a single title. multiple titles would be rare. and, to make it worse,
> there are several instances of this where you need a table but its seems
> overkill for the odd 1% time when you actually need teh extra row.
>
> of course, the there'd be a language lookup table.
>
> what about the speed and query issue?
The book or movie or whatever table should have an index on something
(say bookid). Then make an index on the title table on bookid. This
makes getting the titles for a specific book fairly efficient.
I think using a simpler design (i.e. tables in preference to arrays)
will make doing the project easier. This may override any speed up
you get using arrays.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-09-30 14:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mike Sosteric <mikes@athabascau.ca>
1 sibling, 1 reply; 36+ messages in thread
From: Tom Lane @ 2002-09-30 14:42 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-general@postgresql.org; pgsql-sql
Mike Sosteric <mikes@athabascau.ca> writes:
> could you select where title[0] = 'en'
You certainly could ... but bear in mind that there's no convenient way
to make such a query be indexed, at present. So any values that you
actually want to use as search keys had better be in their own fields.
Now, if you are just using this as an extra search condition that picks
one row out of a small number that are identified by another WHERE
clause, then it's good enough to index for the other clause, and so the
lack of an index for title[0] isn't an issue. In this case, with only
a small number of possible values for title[0], it seems that an index
wouldn't be helpful anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 15:54 Josh Berkus <josh@agliodbs.com>
parent: Mike Sosteric <mikes@athabascau.ca>
3 siblings, 2 replies; 36+ messages in thread
From: Josh Berkus @ 2002-09-30 15:54 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
Mike,
> We are currently developing a database to host some complicated, XMl
> layered data. We have chosen postgres because of its ability to store
> multidimensional arrays. We feel that using these will allow us to
> simplify the database structure considerably by storing some data in
> multidimensional arrays.
Hmmm ... I'm curious; what kind of data do you feel could be
*simplified* by multi-dimensional arrays?
> However, we currently have some dissenters who believe that using the
> multidimensional arrays will make queries slower and unneccesarily
> complicated.
They're correct, especially about the latter.
> 1) are SQL queries slower when extracting data from multidimensional
> arrays
Yes, but this is fixable; see the Intarray package in /contrib.
> 2) are table joins more difficult or unneccesarily complicated
Yes.
> 3) can you do selects on only a portion of a multidimensional array.
Yes.
> That
> is, if you were storing multilanguage titles in a two dimensional
> array,
>
> [en], "english title"
> [fr], "french title"
>
> could you select where title[0] = 'en'
Yes.
> I know these may sound like terribily stupid questions. but we need
> some
> quick guidance before proceeding with a schema that relies on these
> advanced data features of postgres
The problem you will be facing is that Arrays are one of the
fundamentally *Non-Relational* features that Postgresql supports for a
limited set of specialized purposes (mostly buffer tables, procedures,
and porting from MySQL). As such, incorporating arrays into any kind
of complex schema will drive you to drink ... and is 95% likely more
easily done through tables and sub-tables, in any case.
Let's take your example of "title", and say we wanted to use it in a
join:
SELECT movie.name, movie.show_date, movie.title_lang, title.translation
FROM movies JOIN title_langs ON (
movie.title_lang[1] = title_langs.lang OR movie.title_lang[2] =
title_langs.lang OR movie.title_lang[3] = title_langs.lang ... )
... as you can see, the join is extremely painful. Let alone
constructing a query like "Select all movies with titles only in
English and French and one other language." (try it, really)
Then there's the not insignificant annoyance of getting data into and
out of multi-dimensional arrays, which must constantly be parsed into
text strings. And the fact that you will have to keep track, in your
middleware code, of what the ordinal numbers of arrays mean, since
array elements are fundamentally ordered. (BTW, Postgres arrays begin
at 1, not 0)
Now, I know at least one person who is using arrays to store scientific
data. However, that data arrives in his lab in the form of matrices,
and is not used for joins or query criteria beyond a simple "where"
clause.
As such, I'd reccommend one of two approaches for you:
1) Post some of your schema ideas here, and let us show you how they
are better done relationally. The relational data model has 30 years
of thought behind it -- it can solve a lot of problems.
2) Shift over to an XML database or a full-blown OODB (like Cache').
Good luck.
-Josh Berkus
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 15:59 Dan Langille <dan@langille.org>
parent: Mike Sosteric <mikes@athabascau.ca>
3 siblings, 3 replies; 36+ messages in thread
From: Dan Langille @ 2002-09-30 15:59 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> As such, I'd reccommend one of two approaches for you:
>
> 1) Post some of your schema ideas here, and let us show you how they
> are better done relationally. The relational data model has 30 years
> of thought behind it -- it can solve a lot of problems.
Mike,
Just in case you or others think Josh is some crazed lunatic[1] who
doesn't know what he's talking about, I support his views on this
topic. Avoid arrays. Normalize your data.
[1] - Actually, I don't think I know anything about Josh, except that
he's right about normalizing your data.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 16:09 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Dan Langille <dan@langille.org>
2 siblings, 0 replies; 36+ messages in thread
From: Bruce Momjian @ 2002-09-30 16:09 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: Josh Berkus <josh@agliodbs.com>; Mike Sosteric <mikes@athabascau.ca>; pgsql-sql
Dan Langille wrote:
> On 30 Sep 2002 at 8:54, Josh Berkus wrote:
>
> > As such, I'd reccommend one of two approaches for you:
> >
> > 1) Post some of your schema ideas here, and let us show you how they
> > are better done relationally. The relational data model has 30 years
> > of thought behind it -- it can solve a lot of problems.
>
> Mike,
>
> Just in case you or others think Josh is some crazed lunatic[1] who
> doesn't know what he's talking about, I support his views on this
> topic. Avoid arrays. Normalize your data.
>
> [1] - Actually, I don't think I know anything about Josh, except that
> he's right about normalizing your data.
Yes, arrays have a very small window of usefulness, but the window does
exist, so we haven't removed them.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 16:10 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
2 siblings, 0 replies; 36+ messages in thread
From: Dan Langille @ 2002-09-30 16:10 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Mike Sosteric <mikes@athabascau.ca>; pgsql-sql
On 30 Sep 2002 at 12:09, Bruce Momjian wrote:
> Dan Langille wrote:
> > On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> >
> > > As such, I'd reccommend one of two approaches for you:
> > >
> > > 1) Post some of your schema ideas here, and let us show you how they
> > > are better done relationally. The relational data model has 30 years
> > > of thought behind it -- it can solve a lot of problems.
> >
> > Mike,
> >
> > Just in case you or others think Josh is some crazed lunatic[1] who
> > doesn't know what he's talking about, I support his views on this
> > topic. Avoid arrays. Normalize your data.
> >
> > [1] - Actually, I don't think I know anything about Josh, except that
> > he's right about normalizing your data.
>
> Yes, arrays have a very small window of usefulness, but the window does
> exist, so we haven't removed them.
I do not advocate removing them. I do advocate data normalization.
Let's say it's a matter of Do The Right Thing(tm) unless you know
what you're doing.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 16:30 Josh Berkus <josh@agliodbs.com>
parent: Dan Langille <dan@langille.org>
2 siblings, 0 replies; 36+ messages in thread
From: Josh Berkus @ 2002-09-30 16:30 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; Josh Berkus <josh@agliodbs.com>; Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
Dan,
> Just in case you or others think Josh is some crazed lunatic[1] who
> doesn't know what he's talking about, I support his views on this
> topic. Avoid arrays. Normalize your data.
And just because I'm a crazed lunatic, that doesn't mean that I don't
know what I'm talking about.
Um. I mean, "Even if I were a crazed lunatic, that wouldn't mean that
I don't know what I'm talking about."
<grin>
-Josh "Relational Mania" Berkus
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 17:04 Mike Sosteric <mikes@athabascau.ca>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 17:04 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 30 Sep 2002, Josh Berkus wrote:
I have a very good sense of the strengths of relational databases. But
they are also limited when it comes to object orientaed data (like XML
records). I though arrays would be a way to simply the complexity you get
when you try and map objects to relations.
so a couple more questions then
Is Cache open source?
are the XML databases that are evolved and sophisticated enough to use in
production environments.
m
> of thought behind it -- it can solve a lot of problems.
>
> 2) Shift over to an XML database or a full-blown OODB (like Cache').
>
> Good luck.
>
> -Josh Berkus
>
>
>
>
Mike Sosteric <mikes@athabascau.ca> Managing Editor, EJS <http://www.sociology.org/;
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/;
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 17:18 Josh Berkus <josh@agliodbs.com>
parent: Mike Sosteric <mikes@athabascau.ca>
0 siblings, 1 reply; 36+ messages in thread
From: Josh Berkus @ 2002-09-30 17:18 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
Mike,
> I have a very good sense of the strengths of relational databases. But
> they are also limited when it comes to object orientaed data (like XML
> records). I though arrays would be a way to simply the complexity you get
> when you try and map objects to relations.
In my experience, most XML records are, in fact, simple tree structures that
are actually easy to represent in SQL. But I don't know about yours.
Certainly the translation of XML --> SQL Tree Structure is no more complex
than XML --> Array, that I can see.
> Is Cache open source?
No. It's a proprietary, and probably very expensive, database. There are no
open source OODBs that I know of, partly because of the current lack of
international standards for OODBs.
> are the XML databases that are evolved and sophisticated enough to use in
> production environments.
I don't know. The last time I evaluated XML databases was a year ago, when
there was nothing production-quality in existence. But I don't know what
the situation is now.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 17:24 Mike Sosteric <mikes@athabascau.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 17:24 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 30 Sep 2002, Josh Berkus wrote:
thanks for this. we will stick with the relational model.
m
>
> Mike,
>
> > I have a very good sense of the strengths of relational databases. But
> > they are also limited when it comes to object orientaed data (like XML
> > records). I though arrays would be a way to simply the complexity you get
> > when you try and map objects to relations.
>
> In my experience, most XML records are, in fact, simple tree structures that
> are actually easy to represent in SQL. But I don't know about yours.
>
> Certainly the translation of XML --> SQL Tree Structure is no more complex
> than XML --> Array, that I can see.
>
> > Is Cache open source?
>
> No. It's a proprietary, and probably very expensive, database. There are no
> open source OODBs that I know of, partly because of the current lack of
> international standards for OODBs.
>
> > are the XML databases that are evolved and sophisticated enough to use in
> > production environments.
>
> I don't know. The last time I evaluated XML databases was a year ago, when
> there was nothing production-quality in existence. But I don't know what
> the situation is now.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Mike Sosteric <mikes@athabascau.ca> Managing Editor, EJS <http://www.sociology.org/;
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/;
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 17:29 Josh Berkus <josh@agliodbs.com>
parent: Mike Sosteric <mikes@athabascau.ca>
0 siblings, 1 reply; 36+ messages in thread
From: Josh Berkus @ 2002-09-30 17:29 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
Mike,
> thanks for this. we will stick with the relational model.
Hey, don't make your decision entirely based on my advice. Do some
research! I'm just responding "off the cuff" to your questions.
If you do take the relational approach, post some sample problems here and
people can help you with how to represent XML data relationally.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 18:11 Mike Sosteric <mikes@athabascau.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 18:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 30 Sep 2002, Josh Berkus wrote:
Don't worry.
Our biggest problem is that each XML data entry, say
<title=en>This is the title</title>
has an language attribute. if there are, say 67 seperate items, each with
multiple languages, then the comlexity of the table structure skyrockets
because you have to allow for multiple titles, multiple names, multiple
everything.
the resulting relational model is icky to say the least. The question, is
how to simplify that. I had thought arrays would help because you can
store the multiple language strings in a single table along with other
records..
any ideas?
m
>
> Mike,
>
> > thanks for this. we will stick with the relational model.
>
> Hey, don't make your decision entirely based on my advice. Do some
> research! I'm just responding "off the cuff" to your questions.
>
> If you do take the relational approach, post some sample problems here and
> people can help you with how to represent XML data relationally.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
Mike Sosteric <mikes@athabascau.ca> Managing Editor, EJS <http://www.sociology.org/;
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/;
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 18:20 Josh Berkus <josh@agliodbs.com>
parent: Mike Sosteric <mikes@athabascau.ca>
0 siblings, 1 reply; 36+ messages in thread
From: Josh Berkus @ 2002-09-30 18:20 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-sql
Mike,
> has an language attribute. if there are, say 67 seperate items, each with
> multiple languages, then the comlexity of the table structure skyrockets
> because you have to allow for multiple titles, multiple names, multiple
> everything.
This looks soluable several ways.
Question #1: If each record has 67 fields, and each field may appear in
several languages, is it possible for some fields to be in more languages
than others? I.e. if "title-en" and "title-de" exist, does it follow that
"content-en" and "content-de" exist as well? Or not?
Question #2: Does your XML schema allow locall defined attributes? That is,
do some records have entire attributes ("fields" ) that other records do not?
Suggestion #1: Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book
for giving you ideas on how to adapt SQL structures to odd purposes.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-09-30 18:24 Mike Sosteric <mikes@athabascau.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 36+ messages in thread
From: Mike Sosteric @ 2002-09-30 18:24 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Mon, 30 Sep 2002, Josh Berkus wrote:
>
> Question #1: If each record has 67 fields, and each field may appear in
> several languages, is it possible for some fields to be in more languages
> than others? I.e. if "title-en" and "title-de" exist, does it follow that
> "content-en" and "content-de" exist as well? Or not?
yes.
>
> Question #2: Does your XML schema allow locall defined attributes? That is,
> do some records have entire attributes ("fields" ) that other records do not?
yes.
>
> Suggestion #1: Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book
> for giving you ideas on how to adapt SQL structures to odd purposes.
I have ordered the book from amazon.ca
m
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Mike Sosteric <mikes@athabascau.ca> Managing Editor, EJS <http://www.sociology.org/;
Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/;
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-10-01 07:49 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 36+ messages in thread
From: Achilleus Mantzios @ 2002-10-01 07:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mike Sosteric <mikes@athabascau.ca>; Bruno Wolff III <bruno@wolff.to>; pgsql-general@postgresql.org; pgsql-sql
I was wondering why is such a rage against arrays.
I posted 2 very common problems where arrays provide
the only natural (and efficient) fit. (and got no responses)
So it seems to me that:
- Arrays implementation (along with the intarray package) in postgresql
is well performing and stable.
- Some problems shout out for array usage.
- The Array interface is defined in java.sql package.
(I dont know if sql arrays is in some standard but it seems that
Java sees it that way, at least).
- The Array interface is implemented in the official postgresql java
package.
- In some problems replacing arrays according the tradition relational
paradigm would end up in a such a performance degradation, that
some applications would be unusable.
- Oleg and Teodor did a great job in intarray, making array usage
easy and efficient.
Thanx!
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [GENERAL] arrays
@ 2002-10-01 17:43 Roland Roberts <roland@astrofoto.org>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 36+ messages in thread
From: Roland Roberts @ 2002-10-01 17:43 UTC (permalink / raw)
To: pgsql-sql
>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes:
Josh> Now, I know at least one person who is using arrays to store
Josh> scientific data. However, that data arrives in his lab in
Josh> the form of matrices, and is not used for joins or query
Josh> criteria beyond a simple "where" clause.
Indeed, my first attempt to use arrays was to maintain some basic
statistics about a set of data. The array elements where to be
distribution moments and would only be used in "where" clauses. The
problem was that I wanted to be about to update the statistics using
triggers whenever the main data was updated. The inability to access
a specific array element in PL/pgSQL code made this so painful I ended
up just extending a table with more columns.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-10-01 17:52 Josh Berkus <josh@agliodbs.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 36+ messages in thread
From: Josh Berkus @ 2002-10-01 17:52 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mike Sosteric <mikes@athabascau.ca>; Bruno Wolff III <bruno@wolff.to>; pgsql-general@postgresql.org; pgsql-sql
Achilleus,
> I was wondering why is such a rage against arrays.
>
> I posted 2 very common problems where arrays provide
> the only natural (and efficient) fit. (and got no responses)
> So it seems to me that:
All of your points are correct.
Us "old database hands" have a knee-jerk reaction against arrays for
long-term data storage because, much of the time, developers use arrays
because they are lazy or don't understand the relational model instead
of because they are the best thing to use. This is particularly true
of people who come to database development from, say, web design.
In this thread particularly, Mike was suggesting using arrays for a
field used in JOINs, which would be a royal mess. Which was why you
heard so many arguments against using arrays.
Or, to put it another way:
1. Array data types are perfect for storing data that arrives in the
form of arrays or matricies, such as scientific data , or interface
programs that store arrays of object properties.
2. For other purposes, arrays are a very poor substitute for proper
sub-table storage of related data according to the relational model.
3. The distinguishing factor is "atomicity": ask yourself: "is this
array a discrete and undivisible unit, or is is a collection of related
but mutable elements?" If the former, use and array. If the latter,
use a sub-table.
Clearer now?
-Josh Berkus
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [SQL] arrays
@ 2002-10-04 16:08 scott.marlowe <scott.marlowe@ihs.com>
parent: Mike Sosteric <mikes@athabascau.ca>
3 siblings, 0 replies; 36+ messages in thread
From: scott.marlowe @ 2002-10-04 16:08 UTC (permalink / raw)
To: Mike Sosteric <mikes@athabascau.ca>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Sun, 29 Sep 2002, Mike Sosteric wrote:
> On Sun, 29 Sep 2002, Bruce Momjian wrote:
>
> Apologies in advance if there is a more appropriate list.
>
> We are currently developing a database to host some complicated, XMl
> layered data. We have chosen postgres because of its ability to store
> multidimensional arrays. We feel that using these will allow us to
> simplify the database structure considerably by storing some data in
> multidimensional arrays.
the long and the short of it is that arrays are useful to store data, but
should not be used where you need to look up the data in them in a where
clause.
^ permalink raw reply [nested|flat] 36+ messages in thread
end of thread, other threads:[~2002-10-04 16:08 UTC | newest]
Thread overview: 36+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-22 23:40 arrays Brook Milligan <brook@trillium.NMSU.Edu>
1999-12-01 22:34 arrays Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
1999-12-02 10:20 ` Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-02 11:01 ` Marcin Mazurek - Multinet SA - Poznan <m.mazurek@multinet.pl>
1999-12-02 11:11 ` Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-04 02:23 ` Peter Eisentraut <peter_e@gmx.net>
1999-12-04 09:31 ` Stephen Davies <scldad@sdc.com.au>
1999-12-04 14:02 ` John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
1999-12-04 15:05 ` neko@kredit.sth.szif.hu
1999-12-04 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-04 19:45 ` kaiq@realtyideas.com
2000-12-31 21:29 Arrays Albert REINER <areiner@tph.tuwien.ac.at>
2000-12-31 22:28 ` Re: Arrays Oleg Bartunov <oleg@sai.msu.su>
2001-01-11 18:06 Arrays Sharmad Naik <sharmad@im.eth.net>
2002-09-30 00:12 Re: [SQL] arrays Mike Sosteric <mikes@athabascau.ca>
2002-09-30 12:29 ` Bruno Wolff III <bruno@wolff.to>
2002-09-30 12:38 ` Mike Sosteric <mikes@athabascau.ca>
2002-09-30 13:57 ` Bruno Wolff III <bruno@wolff.to>
2002-09-30 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-01 07:49 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-10-01 17:52 ` Josh Berkus <josh@agliodbs.com>
2002-09-30 13:18 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-09-30 15:54 ` Josh Berkus <josh@agliodbs.com>
2002-09-30 17:04 ` Mike Sosteric <mikes@athabascau.ca>
2002-09-30 17:18 ` Josh Berkus <josh@agliodbs.com>
2002-09-30 17:24 ` Mike Sosteric <mikes@athabascau.ca>
2002-09-30 17:29 ` Josh Berkus <josh@agliodbs.com>
2002-09-30 18:11 ` Mike Sosteric <mikes@athabascau.ca>
2002-09-30 18:20 ` Josh Berkus <josh@agliodbs.com>
2002-09-30 18:24 ` Mike Sosteric <mikes@athabascau.ca>
2002-10-01 17:43 ` Roland Roberts <roland@astrofoto.org>
2002-09-30 15:59 ` Dan Langille <dan@langille.org>
2002-09-30 16:09 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-30 16:10 ` Dan Langille <dan@langille.org>
2002-09-30 16:30 ` Josh Berkus <josh@agliodbs.com>
2002-10-04 16:08 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox