from 2 keys to serial

[Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general