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 <t...@sss.pgh.pa.us>
> desmodemone <desmodem...@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.
>
> If you want that to work reliably, you need to mark the primary key
> 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
>
