Обсуждение: from 2 keys to serial

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

On Sun, 14 Dec 2008 20:16:35 +0100
Ivan Sergio Borgonovo <mail@webthatworks.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

instead of redefining the table (and ending up with two tables pointing
to the same sequence) you could also just call nextval() on the target
sequence when inserting into your temp table -- pretty much the same
thing but seems a bit cleaner.

insert into adresses_temp
select
  userid,
  addressid,
  nextval('adresses_destination_addressid_seq') as newaddressid
;


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ivan Sergio Borgonovo
> Sent: Monday, December 15, 2008 8:32 AM
> To: pgsql-general@postgresql.org
> Subject: [SOLVED] Re: [GENERAL] from 2 keys to serial
>
> On Sun, 14 Dec 2008 20:16:35 +0100
> Ivan Sergio Borgonovo <mail@webthatworks.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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general