Ben Young said: > Hi there, I am having a problem with the following code: > > CREATE TABLE temp ( num integer UNIQUE ); > > INSERT INTO temp (num) VALUES (1); > INSERT INTO temp (num) VALUES (2); > INSERT INTO temp (num) VALUES (3); > > UPDATE temp SET num = num+1; > > If the update is really meant to be atomic then this should work, as the > column is still unique afterwards. However, I get a > ERROR: duplicate key violates unique constraint "temp_num_key" > > Is this something I misunderstand about SQL & ACID in general, a known > problem > in Posgresql, or bug which will be fixed sometime. Should I report this as > a > bug? Although the command is atomic, the processing behind the scenes is not. The problem occurs because record one's PK is updated to 2, but record 2 already has the PK value 2, so you get the PK unique constraint error message. One way round this is to create a procedure to select the records in reverse order (select * from temp order by num DESC), and increment the PK value in a loop. Or update the PK values in two stages adding and then subtracting some number > highest current PK value (inefficient/wasteful): UPDATE temp SET num = num+10000000+1; UPDATE temp SET num = num-10000000; Hope that helps. John Sidney-Woollett ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
