wikipedia article - Mailing list pgsql-novice

From Ilya S. Slyzhnyak
Subject wikipedia article
Date
Msg-id 000501c4478c$ac537280$f980ae0a@odt.pvt
Whole thread Raw
Responses Re: wikipedia article
List pgsql-novice
Here is an article at wikipedia (http://en.wikipedia.org/wiki/PostgreSQL)
And here is a question: how relation between tables user and address have to
be declared?


<QUOTE>
Another very useful feature is that PostgreSQL is capable of directly
understanding the relationships that exist between tables. People in the
real world typically have several addresses, which in the relational model
is stored by placing the addresses in one table and the rest of the user
information in another. The addresses are "related" to a particular user by
storing some unique information, say the user's name, in the address table
itself. In order to find all the addresses for "Bob Smith", the user writes
a query that "joins" the data back together, by selecting a particular name
from the users table and then searching for that name in the address table.
Doing a search for all the users in New York is somewhat complex, requiring
the database to find all the user names in the address table, then search
the user table for those users. A typical search might look like this:
SELECT u.* FROM user u, address a WHERE a.city='New York' and
a.user_name=u.user_name

In PostgreSQL the relationship between users and addresses can be explicitly
defined. Once defined the address becomes a property of the user, so the
search above can be simplified greatly to:

SELECT * FROM user WHERE address.city='New York'

No "join" is required, the database itself understands the user.address
relationship. A related example shows the power of types, if one uses
Postgres to do:

SELECT address FROM user

The results will be broken out automatically, returning only those addresses
for users, not those for companies or other objects that might be using the
address table.

</QUOTE>


---
WBR, Ilya S. Slyzhnyak
Information Technology Center, Kazakhtelecom
Ust-Kamenogorsk, Kazakhstan
mobile: +7-333-2396186


pgsql-novice by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: ON DELETE CASCADE (was: Modifying an existing
Next
From: Derrick Betts
Date:
Subject: Re: Dynamic Array into pl/pgSQL function