pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Gemeinschaft Studienarbeit Datenbanken <oodbms@floppy.org>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] select-querries out of an array?
Date: Tue, 6 Oct 1998 17:09:41 +0200
Message-ID: <l03110706b23fdff8eec3@[147.233.159.109]> (raw)
In-Reply-To: <199810061603.3142469.6@onyx.floppy.org>

At 17:03 +0200 on 6/10/98, Gemeinschaft Studienarbeit Datenbanken wrote:


> When I now would like to have all games (Spiel*) selected, which come
> on CD as media (Datentraegertyp is array which contains CD, Disks, misc,
> etc.), I would type the sql request like this:
>
> select name from Spiel* where Datentraegertyp = '{"CD"}';
>
> which only lists all games which *ONLY* come on CD (pretty clear as the
> match is '=' on the single array entry. How do I formulate the request when
> I would like to have those which have Datentraegertyp like '{"CD",
>"Diskette"}'
> or '{"Tape", "Diskette", "CD"}' ?
>
> I hope you get what I am up to ;-)
>
> Thanks once again for hints.

I never liked arrays in databases. They are not the right implementation,
at least not from the classical relational point-of-view, for what you have
in mind.

I tend to look at arrays as representing a bulk of information (An icon, an
IP). And that's exactly what you can do with it in PostgreSQL: Retrieve the
entire array and use it within the application, write an entire array, etc.

The relational approach to your configuration is to have a second table,
relating to your main table through the primary key. So, it has two fields
- "Bestellnummer" (whatever that is) and "Datentraegertyp". For one game in
the Spiel* tables, you can have several rows in this secondary table - one
row for each media on which this particular game appears.

Then you join.

SELECT ....
FROM Spiel* s, MediaTable m
WHERE .....
  AND s.Bestellnummer = m.Bestellnummer
  AND m.Datentraegertyp in ( 'CD', 'Diskette', 'Tape' );

I haven't tried this with an object-oriented table (with that * after the
table name).

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (2+ messages)

Message-ID: <l03110706b23fdff8eec3@[147.233.159.109]>
Permalink:  ../l03110706b23fdff8eec3@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110706b23fdff8eec3@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, oodbms@floppy.org
  Subject: Re: [SQL] select-querries out of an array?
  In-Reply-To: <l03110706b23fdff8eec3@[147.233.159.109]>
  References: <199810061603.3142469.6@onyx.floppy.org> <l03110706b23fdff8eec3@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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