Skip site navigation (1) Skip section navigation (2)

RE: [SQL] getting the values of a complex attribute

From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
To: Nicolas(dot)Caillaud(at)mail(dot)dotcom(dot)fr, pgsql-sql(at)postgreSQL(dot)org
Subject: RE: [SQL] getting the values of a complex attribute
Date: 1998-06-08 18:12:21
Message-ID: F10BB1FAF801D111829B0060971D839F2C58C5@cpsmail (view raw or flat)
Thread:
Lists: pgsql-sql
> Hi *
> 
> I'm new to SQL, and being in the Linux world, I began with Postgresql.
> One day, I wrote the following :
> 
> - create table person (name text);
> - create table book(name text, author person);
> 
> simple, isn't it ? ;()
sure...

> Now :
> 
> - insert into person (name) values ('nicolas');
> INSERT 691704 1
> - insert into person (name) values ('julien');
> INSERT 691705 1
> 
> now, to fill book :
> - insert into book (name, author) values ('my book', 691704);
> INSERT 691706 1
> 
> ok, but cant i write anything but the oid of the person record i want
> to
> insert ? (this is my first question)
Not in that table definition.

> Next :
> insert into book (name,author) values ('other ', 691705);
> INSERT 691707
> 
> OK, right now, i want te get all books written by 'nicolas', i want to
> write
> 
> select * from book where author.name = 'nicolas';
> 
> ERROR:  author: Table does not exist.
> 
> select * from book where author = 691706;
> 
> ERROR:  There is no operator '=' for types 'person' and 'int4'
>         You will either have to retype this query using an explicit
> cast,
>         or you will have to define the operator using CREATE OPERATOR
> 
try:
 select * from book 
  where EXISTS(select person.oid from person 
                where person.oid = book.author and 
                      person.name = 'nicolas');
or
 select book.* from book, person
  where book.author = person.oid and
        person.name = 'nicolas';
use the faster one.

> Now my second question : what can i do to get (in a simpler manner)
> all the books written by 'nicolas' ?
> 
> I think that writing an operator just to do that is a little bit
> complicated :-((
> 
> Thank you if someone got the answer .......
> 
> --
> Nicolas Caillaud
> Nicolas(dot)Caillaud(at)mail(dot)dotcom(dot)fr
> 
> 
> 

Responses

pgsql-sql by date

Next:From: Nicolas CaillaudDate: 1998-06-08 21:52:52
Subject: Re: [SQL] getting the values of a complex attribute
Previous:From: Mike PayneDate: 1998-06-08 14:42:25
Subject: (no subject)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group