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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
