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