Thread: elegant way to fill a table with serial

I've to fill something like:

create table DESTtable1 (
  pk1 serial primary key,
--  rest of stuff
);

create table DESTtable2 (
  pk2 serial primary key,
  fk1 int references DESTtable1(pk1)
-- rest of stuff
);

from data that are such way

create table SRCtable1 (
  pk1 serial primary key,
--  rest of stuff
);

create table SRCtable2 (
  fk1 int references DESTtable1(pk1)
  ak int not null,
-- rest of stuff
  unique(fk1,aa)
);

substantially (fk1,aa) -> pk2


the best way I thought is:

create table temp1 (
  pk2 serial primary key,
  fk1 int references DESTtable1(pk1),
  ak int not null
);

start transaction;

insert into temp1 (fk1,ak) select ....

insert into DESTtable2 (pk2,fk1,...)
  select (pk2,fk1...) from SRC2
  join temp1 ...
);

setval('....',currval('...'));

commit;

But it looks awful. I did it. It worked... but it is awful!

BTW I saw there is no OWNED BY in 8.1, is there any other way to
auto-drop sequences when columns get dropped?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> ... BTW I saw there is no OWNED BY in 8.1, is there any other way to
> auto-drop sequences when columns get dropped?

In previous versions, a sequence object that was auto-created as a
result of a SERIAL column declaration will be auto-dropped when that
column is dropped.  8.2's OWNED BY syntax is just a way of exposing
that mechanism more formally, and letting you tweak the state.

            regards, tom lane