Re: skip duplicate key error during inserts

On Oct 27, 2006, at 14:56 , Ron Johnson wrote:

>> I think you completely missed that I am recommending using '\set
>> ON_ERROR_ROLLBACK on' in psql.
>>
>> Please refer to my previous post and see the effect of the
>> following line:
>>
>> postgres=# \set ON_ERROR_ROLLBACK on
>
> But I do *not* want my whole transaction to roll back!!

That is not what is happening. From the documentation:

> ON_ERROR_ROLLBACK
> When on, if a statement in a transaction block generates an error,
> the error is ignored and the transaction continues. When
> interactive, such errors are only ignored in interactive sessions,
> and not when reading script files. When off (the default), a
> statement in a transaction block that generates an error aborts the
> entire transaction. The on_error_rollback-on mode works by issuing
> an implicit SAVEPOINT for you, just before each command that is in
> a transaction block, and rolls back to the savepoint on error.

So with on_error_rollback the transaction continues regardless of
errors:

# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR:  duplicate key violates unique constraint "t2_pkey"
alex # insert into t2 values ( 1 );
ERROR:  duplicate key violates unique constraint "t2_pkey"

With on_error_rollback disabled, the transaction is implicitly aborted:

# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR:  duplicate key violates unique constraint "t2_pkey"
# insert into t2 values ( 1 );
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

The wording of the option (in combination with the value "on") is
admittedly confusing. It's really "on_error_continue".

Alexander.