Um, the behaviour you are seeing is what would happen in PostgreSQL if
everything were all in one transaction. What you show for Oracle is what
would happen if each statement were in it's own transaction.
On the postgresql server here, without transactions:
create temp table test (a int primary key);
insert into test values (1); -> ok
insert into test values (2); -> ok
insert into test values (1); -> duplicate key
insert into test values (3); -> ok
With transactions you get:
create temp table test (a int primary key);
insert into test values (1); -> ok
begin;
insert into test values (2); -> ok
insert into test values (1); -> duplicate key
insert into test values (3); -> aborted transaction
abort;
These are both correct behaviour AFAIK.
Hpoe this helps,
On Mon, Jul 14, 2003 at 09:47:30AM +0200, Jörg Schulz wrote:
> Suppose the following:
>
> create table test (a int primary key);
> insert into test values (1);
>
> select * from test;
> a
> =
> 1
>
> In Postgresql if you do the following in a transaction (either with
> autocommit=off or with an explizit begin):
>
> insert into test values (2); -> ok
> insert into test values (1); -> error (duplicate key)
> insert into test values (3); -> error (transaction aborted)
> commit;
>
> You get:
>
> select * from test;
> a
> =
> 1
>
>
> In Oracle/MySQL if you do the same you get:
>
> insert into test values (2); -> ok
> insert into test values (1); -> error (duplicate key)
> insert into test values (3); -> ok
> commit;
>
> select * from test;
> a
> =
> 1
> 2
> 3
>
> Which behavior is right?
> Is there a way to make Postgresql behave like the other databases?
> Which other Databases act like Postgresql and which do it like Oracle/MySQL?
>
> Jörg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington