[SOLVED] Re: from 2 keys to serial

On Sun, 14 Dec 2008 20:16:35 +0100
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

The cleanest solution I was able to find was to redefine the
addresses_temp table so that it uses the same sequence as the _dest
table.

Some general design advices would be still welcome.

> 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.

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