Re: how to determine OID of the row I just inserted???
От | will trillich |
---|---|
Тема | Re: how to determine OID of the row I just inserted??? |
Дата | |
Msg-id | 20030206173142.GC17263@mail.serensoft.com обсуждение исходный текст |
Ответ на | Re: how to determine OID of the row I just inserted??? (Dennis Gearon <gearond@cvc.net>) |
Список | pgsql-general |
> 2/6/2003 6:08:17 AM, "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> wrote: > >Here's a question I have asked some time ago and Google tells me I'm > >not the only one with this problem, but I haven't found a solution yet > >:-(. I have a setup like this: > > > > customer {id serial, name varchar, address bigint} > > person {id serial, name varchar, address bigint} > > address {id serial, street varchar} > > > > customer.address points to address.id > > person.address points to address.id i finally figured out the the serial datatype is really an int4 (with auto-incrementing insert as default): \d team Table "team" Column | Type | Modifiers --------+---------+--------------------------------------------------- id | integer | not null default nextval('"_track_id_seq"'::text) <snip> they're int (int4) not int8. at least, on my debian box, that is. assuming your address table's id is also auto-incremented using the nextval() function, then here's what you can do -- insert into address( street )values( 'Penny Lane' ); -- now "currval" is available for the id, this session update customer set address = currval('address_id_seq') where id = $yadayada; === but here's a question for you -- does each location exist independelty of who it's a location for? and can customers or persons not have more than one address? (person->work, home, vacation, etc; customer->east, hongKong, downtown, etc) maybe you should have a person.id or customer.id field in your address table: create table address_type( id serial, name varchar(20), seq smallint, -- for ordering choices on an interface "menu" primary key ( id ) ); insert into address_type(name,seq)values('Home',10); insert into address_type(name,seq)values('Office',20); insert into address_type(name,seq)values('Friend',100); insert into address_type(name,seq)values('Family',110); insert into address_type(name,seq)values('Vacation',200); create table address( id serial, person int4 references person ( id ), address_type int4 references address_type ( id ), street varchar(40), primary key ( id ) ); then for a particular person, insert into address(person,address_type,street) select p.id, t.id, 'Penny Lane' from person p, address_type t where p.lname = 'Pfingston' -- or whatever you need to specify the ONE person and t.name = 'Home' ; then join address.person to person.id, instead of the other way 'round. === one more note -- having perused "database design for mere mortals" i agree it's important to separate the subjects from their attributes... is "address" really an appropriate name for a table? it's a bit ambiguous, that term -- can mean "number-and-street" or "everything needed to get the delivery taken care of", but we take it to mean the street portion of a location spec: 329 main street <== address suote 701 <== suite centralburg <== city idaho <== province/state 87654 <== postal code usa <== country code 329 main street <== address <== suite galt's gulch <== city colorado <== province/state 77665 <== postal code usa <== country code these are locations, right? for any particular geographic location, we consider an address to be one portion of the total picture. we call ours "location" of which "address" is a field. just a thought... -- There are 10 kinds of people: ones that get binary, and ones that don't. will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! Looking for a firewall? Do you think smoothwall sucks? You're probably right... Try the folks at http://clarkconnect.org/ !
В списке pgsql-general по дате отправления:
Предыдущее
От: Shannon Eric PeeveyДата:
Сообщение: installations of PostgreSQL housing visual resources