Re: BUG or strange behaviour of update on primary key

Hello there
                   Thanks Tom!
By the way I find something very funny :

Oracle 11gR2 :

SQL> create table testup ( a number ) ;

Tabella creata.

SQL> alter table testup add primary key (a) NOT DEFERRABLE INITIALLY IMMEDIATE ;

Tabella modificata.

SQL> insert into testup values (1 ) ;

Creata 1 riga.

SQL>  insert into testup values (2 ) ;

Creata 1 riga.

SQL> commit ;

Commit completato.

SQL> update testup set a=a+1 ;

Aggiornate 2 righe.   -->>> Oracle Bug ??

SQL> commit ;

Commit completato.


Postgresql :


create table  testup  ( a int ) ;

alter table testup add primary key(a) NOT DEFERRABLE INITIALLY IMMEDIATE ;

 insert into testup values (1);

insert into testup values (2);

update testup set a=a+1 ;
ERROR:  duplicate key value violates unique constraint "testup_pkey"




Like Tom correctly says :

alter table  testup  DROP   CONSTRAINT testup_pkey ;

alter table testup add primary key(a)  DEFERRABLE INITIALLY IMMEDIATE ;

 update testup set a=a+1 ;
UPDATE 2

commit;


Seems an Oracle bug not Postgresql one!

Regards, Mat