[SOLVED] Re: from 2 keys to serial

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: <pgsql-general(at)postgresql(dot)org>
Subject: [SOLVED] Re: from 2 keys to serial
Date: 2008-12-15 16:32:04
Message-ID: 20081215173204.50a44d97@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Fitzpatrick 2008-12-15 16:32:39 Setting NEW in AFTER trigger
Previous Message Adam Rich 2008-12-15 16:27:39 Re: View vs Constantly Updated Table