pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedset type and in clause
3+ messages / 3 participants
[nested] [flat]
* set type and in clause
@ 1999-01-19 22:25 frank <frank@x9media.com>
1999-01-19 23:58 ` Re: [SQL] set type and in clause Brian Baquiran <brianb@evoserve.com>
1999-01-20 10:11 ` Re: [SQL] set type and in clause Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 3+ messages in thread
From: frank @ 1999-01-19 22:25 UTC (permalink / raw)
To: pgsql-sql@hub.org
i suppose it´s been asked before (i´ve seen the question, but not the
answer, in the mailing list archives):
how do you create a set type, i.e. an attribute which is, say ´set of
char´; and then you´d want to run some query like
select . . . from . . . where xxx in yyy
is such a data type supported by postgresql? do other dbms´s support it?
cheers
frank
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] set type and in clause
1999-01-19 22:25 set type and in clause frank <frank@x9media.com>
@ 1999-01-19 23:58 ` Brian Baquiran <brianb@evoserve.com>
1 sibling, 0 replies; 3+ messages in thread
From: Brian Baquiran @ 1999-01-19 23:58 UTC (permalink / raw)
To: frank <frank@x9media.com>; +Cc: pgsql-sql@hub.org
On Tue, 19 Jan 1999, frank wrote:
> how do you create a set type, i.e. an attribute which is, say ´set of
> char´; and then you´d want to run some query like
>
> select . . . from . . . where xxx in yyy
>
> is such a data type supported by postgresql? do other dbms´s support it?
Perhaps you mean an array? If so, it's in Ch. 13 of your postgres docs.
Don't know if other dbms support it.
Brian
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] set type and in clause
1999-01-19 22:25 set type and in clause frank <frank@x9media.com>
@ 1999-01-20 10:11 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1999-01-20 10:11 UTC (permalink / raw)
To: frank <frank@x9media.com>; +Cc: pgsql-sql
At 0:25 +0200 on 20/1/99, frank wrote:
>
> i suppose it´s been asked before (i´ve seen the question, but not the
> answer, in the mailing list archives):
>
> how do you create a set type, i.e. an attribute which is, say ´set of
> char´; and then you´d want to run some query like
>
> select . . . from . . . where xxx in yyy
>
> is such a data type supported by postgresql? do other dbms´s support it?
According to the relational model, a set is a separate table which is
connected to your main table by a foreign key. That is, you hold two tables:
Main table:
P.Key 1:1 Data
----- --------
10 Microsoft
20 Adobe
30 FSF
...
Secondary table:
F.Key 1:N Data
----- --------
10 Office
10 Access
20 Photoshop
30 gcc
10 Windows
20 Acrobat
30 bison
30 flex
In this manner, you have the strings 'Office', 'Access' and 'Windows'
associated with the record for 'Microsoft', the strings 'Photoshop' and
'Acrobat' associated with the record for 'Adobe', and the strings 'gcc',
'bison' and 'flex' associated with the record for 'FSF'.
Suppose the names of the fields are 'p' for the primary key, 'company' form
the 1:1 data, 'f' for the foreign key and 'product' for the 1:N data, you
can write a query like this:
SELECT company, product
FROM main, secondary
WHERE p=f.
The result would be:
Microsoft Office
Microsoft Access
Microsoft Windows
Adobe Photoshop
Adobe Acrobat
FSF gcc
FSF bison
FSF flex
The point I am trying to get across is that this, in fact, represents a set
associated with each record. You get the data you wanted. The problem is
that in the result you get repetitions, because that's how the relational
model works. If you want to isolate the set, you can use a frontend which
drops the repetitions and creates the following report from them:
Microsoft Office
Access
Windows
Adobe Photoshop
Acrobat
FSF gcc
bison
flex
Nevertheless, the data represented by this report is the same data that you
got from the original query. It merely shows association with the company.
So, if you wanted to test whether something belongs or does not belong to a
set that is associated with one of your software vendors, you can do a
subquery on the table. For example, to get the companies which have in
their set of products a product called 'Acrobat', you write:
SELECT company
FROM main
WHERE 'Acrobat' IN (
SELECT product
FROM secondary
WHERE p=f );
I'd like to note that having the set of related products included in the
main table is, well, not relational.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-01-20 10:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-19 22:25 set type and in clause frank <frank@x9media.com>
1999-01-19 23:58 ` Brian Baquiran <brianb@evoserve.com>
1999-01-20 10:11 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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