RE: Object features of pg - Mailing list pgsql-sql

On Tue, 3 Oct 2000, Michael Ansley wrote:

> I suspected as much of the querying, but I can't even get data into the
> tables.  I tried this:
> 
> insert into client values ('Michael');
> insert into address values ('11 Windsor Close, 'RH16 4QR');
> INSERT 18935 1
> update client set address = 18935 where name = 'Michael';
> ERROR:  Attribute 'address' is of type 'address' but expression is of type
> 'int4
> '
>         You will need to rewrite or cast the expression

It worked for me (with 7.0.2 on linux/i386) when I tried it with a
cast like this:

update client set address = 18935::address where name = 'Michael';

I wasn't aware that a table name could be used as a type (which I assume
is represented as an integer internally).  Is that in the docs?

Probably it's better to use referential integrity checks if you want to
ensure type checking like this (it will also ensure that the value pointed
to actually exists in the referenced table).  (see
http://www.postgresql.org/docs/aw_pgsql_book/node153.html under PRIMARY
KEY for details).
-- 
Tod McQuillin


P.S.  If there is a one to one relationship between clients and addresses,
you probably don't really want to have them in separate tables.

It's more useful to have separate tables when there's a many-to-one
relationship, like when more than one client can have the same address, so
they both store a pointer to the same row in the address table.

But maybe that model doesn't work so well for addresses.  What if one of
the clients moves?  If you update the address record that another client
is also pointing to, it looks like they both moved.






> 
> So that didn't work, and I tried casting the oid, but that didn't seem to
> work either.  Any ideas?
> 
> Cheers...
> 
> 
> 
> >>   -----Original Message-----
> >>   From: Peter Mount [mailto:peter@retep.org.uk]
> >>   Sent: 03 October 2000 14:32
> >>   To: Michael Ansley
> >>   Cc: 'pgsql-sql@postgresql.org'
> >>   Subject: Re: [SQL] Object features of pg
> >>   
> >>   
> >>   On Tue, 3 Oct 2000, Michael Ansley wrote:
> >>   
> >>   > I've done the following:
> >>   > 
> >>   > create table address (address varchar(50), postcode varchar(9));
> >>   > create table client(id integer, name varchar(30), 
> >>   address address);
> >>   > 
> >>   > Now, how the hell do I get information into the address 
> >>   field of client?
> >>   > There appears to be very little in the manual dealing 
> >>   with PGs object
> >>   > features.  Any pointers to places in the manual, or 
> >>   direct instructions
> >>   > would be gratefully accepted.
> >>   
> >>   Something like:
> >>   
> >>   select id,name,a.address,postcode
> >>   from client c, address a
> >>   where c.address=a.oid;
> >>   
> >>   Probably neater ways of doing it...
> >>   
> >>   Peter
> >>   
> >>   -- 
> >>   Peter T Mount peter@retep.org.uk http://www.retep.org.uk
> >>   PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
> >>   Java PDF Generator http://www.retep.org.uk/pdf/
> >>   
> >>   
>