from 2 keys to serial - Mailing list pgsql-general

I've to import something whose schema looks like

create table user(
  userid serial primary key,
);

create table adresses_source(
  userid int references user(userid),
  addressid int
);

where addressid are a sequence for each userid as:

1,1
1,2
1,3
2,1
2,2
3,1
4,1
4,2
4,3

to something that should be like

create table adresses_destination(
  userid int references user(userid),
  addressid serial primary key
);

Currently I was using a temp table

create table adresses_temp(
  userid int references user(userid),
  addressid int,
  newaddressid serial primary key
);

and then simply copy to the final destination.

But then I need to sync sequences since filling adresses_destination
from adresses_temp doesn't increment the sequence.

I need some kind of temp table since I need to keep the relationship
between

adresses_source.(userid, addressid) -> adresses_destination.addressid

since I've to deal with other related tables.

Syncing between sequences happens

select setval('sequence_of_addresses_dest',
currval('sequence_of_addresses_temp'));

sort of...

but the above select is far from being elegant and fail if no rows
were inserted.

I'd bet that the problem of transforming 2 keys into a serial is
pretty common and I'm asking for any alternative more elegant way
than the above.

thanks

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