On 2009-04-08, Stuart McGraw <smcg2...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
