From: | Simon Drabble <simond(at)foxlink(dot)net> |
---|---|
To: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
Cc: | Carlos Peralta Ramirez <cperalta(at)hera(dot)inf(dot)ucv(dot)cl>, Postgres Group <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] Selecting from arrays |
Date: | 1999-05-09 14:42:13 |
Message-ID: | Pine.LNX.3.96.990509104014.22324H-100000@dragon |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 9 May 1999, Herouth Maoz wrote:
> At 22:42 +0300 on 08/05/1999, Simon Drabble wrote:
>
>
> > I'd rather not have to code a function for something which, it seems to
> > me, should be part of the installation. The application is eventually
> > intended for several platforms all of which might be running different
> > DBMS's, so I'd like to know if there's a common SQL way to do this and if
> > so if Postgres supports it.
>
> If you want to do it in the SQL way, you won't use arrays. They are, at
> least in this use, contrary to the relational model.
>
> What you really should do is have two tables:
>
> One (results) should have:
> id (primary key)
> date
> location
>
> The other (temperatures) should have
> id (foreign key into the above table)
> temperature
>
> You enter each temperature into the second table along with the id of the
> record to which it belongs. And then your query is super standard:
>
> SELECT DISTINCT date, loc
> FROM results, temperatures
> WHERE results.id = temperatures.id
> AND value = temperature;
>
> Or (more legible, less efficient):
>
> SELECT date, loc
> FROM results
> WHERE value in (
> SELECT temperature
> FROM temperatures
> WHERE temperatures.id = results.id
> );
>
> Don't forget to create and index on the ID field.
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
Hmm. I had thought of doing it this way. When I was investigating possible
solutions I thought arrays looked mighty elegant, but it seems that since
they are non-standard I will have to use a separate table, as you outlined
above.
Thanks to you and Chris for your help.
Simon.
--
"When a German dwarf dances with the butcher's son.." -- Tom Waits
Simon Drabble Somewhere in cyberspace
simond(at)foxlink(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Jonny Hinojosa | 1999-05-09 14:49:45 | RE: [GENERAL] Regression test failures |
Previous Message | Simon Drabble | 1999-05-09 14:39:38 | Re: [GENERAL] Selecting from arrays |