pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tod McQuillin <devin@spamcop.net>
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
Cc: 'Peter Mount' <peter@retep.org.uk>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: RE: Object features of pg
Date: Wed, 4 Oct 2000 00:37:37 +0900 (JST)
Message-ID: <Pine.LNX.4.21.0010040028320.1978-100000@glass> (raw)
In-Reply-To: <7F124BC48D56D411812500D0B747251406144A@fileserver002.intecsystems.co.uk>

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/
> >>   
> >>   
> 




view thread (8+ messages)  latest in thread

Message-ID: <Pine.LNX.4.21.0010040028320.1978-100000@glass>
Permalink:  ../Pine.LNX.4.21.0010040028320.1978-100000@glass/
Also on:    postgresql.org/message-id/Pine.LNX.4.21.0010040028320.1978-100000@glass

 · 

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: devin@spamcop.net, Michael.Ansley@intec-telecom-systems.com, peter@retep.org.uk
  Subject: Re: Object features of pg
  In-Reply-To: <Pine.LNX.4.21.0010040028320.1978-100000@glass>
  References: <7F124BC48D56D411812500D0B747251406144A@fileserver002.intecsystems.co.uk> <Pine.LNX.4.21.0010040028320.1978-100000@glass>

* 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