Re: skip duplicate key error during inserts

[Alexander Staubo <alex(at)purefiction(dot)net>]

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.