From: Stuart McGraw <smcg2297@frii.com>
To: pgsql-sql@postgresql.org
Subject: Re: changing multiple pk's in one update
Date: Mon, 13 Apr 2009 10:53:47 -0600
Message-ID: <49E36E1B.6020107@frii.com> (raw)
In-Reply-To: <grnl9e$49a$1@reversiblemaps.ath.cx>
References: <49DC1034.1060705@frii.com>
<grnl9e$49a$1@reversiblemaps.ath.cx>
Jasen Betts wrote:
> On 2009-04-08, Stuart McGraw <smcg2297@frii.com> wrote:
>> Hello all,
>>
>> I have a table with a primary key column
>> that contains sequential numbers.
>>
>> Sometimes I need to shift them all up or down
>> by a fixed amount. For example, if I have
>> four rows with primary keys, 2, 3, 4, 5, I
>> might want to shift them down by 1 by doing:
>>
>> UPDATE mytable SET id=id-1
>>
>> (where "id" is the pk column) so that the pk's
>> are now 1, 2, 3, 4.
>>
>> When I try to shift them up by using +1 in the
>> above update statement, I get (not surprisingly)
>> a duplicate key error. I also realize that the
>> -1 case above works only by luck.
>>
>> So my question:
>> Is there some way, perhaps with ORDER BY, that
>> I can achieve the change I want with a single
>> update statement? (If I have an unused key
>> range large enough, I suppose I could update
>> all the keys to that range, and then back to
>> my target range but the requires two updates
>> (there are a lot of foreign keys referencing
>> these primary keys) and requires that I have
>> an available range, so a single update statement
>> would be preferable.)
>>
>> Thanks for any enlightenment.
>
> begin a transaction
> suspend the constraint (use SET CONSTRAINTS ... DEFERRED)
> drop the index
> do the update(s)
> recreate the index
> commit the transaction.
>
> I see no reason to keep the index (and its associated UNIQUE
> constraint) during the update, AFAICT all it does is slow the process
> down.
Thanks for the suggestion.
Unfortunately I am doing this key renumbering in
an interactive gui app and as there are several million
rows involved, rebuilding indexes take too long.
I have continued searching for other ways to do this
but most of my google results are floating in a sea
of "update if insert fails, like mysql" results so
I still have a little hope it is possible.
I thought I remember seeing, a year or two ago, an
update statement with an ordered subquery that avoided
duplicate key errors but I am probably misrembering.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smcg2297@frii.com
Subject: Re: changing multiple pk's in one update
In-Reply-To: <49E36E1B.6020107@frii.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox