Re: Catching errors inside transactions

On Wednesday 15 May 2002 14:25, John Taylor wrote:
> Hi all,
>
> I'm accessing postgres (7.1) through JDBC.
>
> My application performs a number of UPDATEs, and INSERTs to the database.
> If there are any SQL errors, I catch them and deal with them as appropriate
> in my code.
>
> However, I now want to put these inside a transaction.
>
> This all works fine, except that if there is an error, postgress automatically
> rolls back the transaction, even though I COMMIT at the end.
>
> Is there a way I can stop the errors causing the transaction to fail, or to force
> the transaction to commit ?
>
> I don't really want to explicitly check for the existence of a record before an UPDATE/INSERT,
> as errors are the exception.
>

OK,
I now understand that the errors cannot be caught, but after digging through the mailing lists I
came accross this:

> A duplicate key in index error will result in the entire transaction
> needing to be rolled back. So what you are proposing to do can't be
> done with postgres. However the way I work around this
> problem is to do
> the following:
>
> insert into foo (bar) values (?)
> where not exists select * from foo where bar = ?
>
> Inserts of this format will prevent duplicates from being
> inserted. You
> can even look at the result of the above statement to see the
> number of
> rows affected, and if it is zero (meaning the row was already
> there and
> you didn't insert), you can branch and do an update instead.

It seems to be exactly what I want to do, but it doesn't appear to be a valid SQL syntax.
Am I missing something ?

Thanks
JohnT