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
2011/10/18 Tom Lane
<tgl@sss.pgh.pa.us>desmodemone <
desmodemone@gmail.com> writes:
> create table testup ( a int ) ;
> alter table testup add primary key (a ) ;
> 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"
> DETTAGLI: Key (a)=(2) already exists.
constraint as deferred. By default, uniqueness is checked immediately
when a row is inserted or updated --- and here, when you update 1 to 2,
it's not unique because the second row hasn't been visited yet.
regards, tom lane