Message-ID: <49DC1034.1060705@frii.com>
Date: Tue, 07 Apr 2009 20:47:16 -0600
From: Stuart McGraw <smcg2297@frii.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: changing multiple pk's in one update
Content-Type: text/plain; charset=ISO-2022-JP
Content-Transfer-Encoding: 7bit

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.