Re: [GENERAL] Selecting from arrays
От | Simon Drabble |
---|---|
Тема | Re: [GENERAL] Selecting from arrays |
Дата | |
Msg-id | Pine.LNX.3.96.990509104014.22324H-100000@dragon обсуждение исходный текст |
Ответ на | Re: [GENERAL] Selecting from arrays (Herouth Maoz <herouth@oumail.openu.ac.il>) |
Список | 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@foxlink.net
В списке pgsql-general по дате отправления: