that error is correct:
CREATE TABLE test.test
(
a int4 NOT NULL DEFAULT nextval(' test.test_a_seq'::regclass),
CONSTRAINT pk PRIMARY KEY (a)
)
WITHOUT OIDS;
And then try an update:
lstore=> select * from test.test;
a
----
1
2
3
4
5
6
7
8
9
10
lstore=> update test.test set a = a + 2 where a >= 3;
ERROR: duplicate key violates unique constraint "pk"
it starts anywhere in the table and updates line by line. So if it starts with, say, a=4, it trys to set a=4+2, giving 6 which is allready present.
possible solution: create a temp table from a select with that a+x, and then freshen your data from there.
On a side node ... if you have to change your PRIMARY KEY in this fashion, there is propably a design error within your database scheme / application.
Best wishes
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold
