To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: changing multiple pk's in one update
Date: 10 Apr 2009 14:32:14 GMT
Organization: Dis (not Dat) Organisation
Lines: 46
Message-ID: <grnl9e$49a$1@reversiblemaps.ath.cx>
References: <49DC1034.1060705@frii.com>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: slrn/pre1.0.0-2 (Linux)

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.