Re: [Pypgsql-users] Automatic rollback on error, duplicate key?

[<py...@ru...>]

On Tue, 12 Nov 2002, Billy G. Allie wrote:

> > I'd might as well also ask how to detect different error codes. The
> > inserts into table 2 may fail for other reasons, and in those cases I
> > should do a rollback. I only want to continue if the error is trying
> > to insert a duplicate key. How am I supposed to do that, perform a
> > regex on the error message string? Yuck. Are there error codes in the
> > cursor or connection to look at?
>
> You could do a search on table 2 for the record you are about to insert to see if it exists.  You can then add it if it wasn't found.  That way you avoid the automatic rollback cause by the duplicate key error.

Yep, that's my workaround. I just wanted to explore the issue a bit to
see why I needed to do this. I thought it was something the driver was
doing.

> Alternatively, is the problem with duplicate int8 values in table 2.  Can you use a serial field to assign the values when they are inserted?  This will guarantee uniqueness.  Later versions of PostgreSQL allow for 8 byte serial fields.

No, the int8 values come from somewhere else. This is really a case of
coming across duplicate data that I only need entered into my table
once. And for these situations I've always just done the insert and
trapped the error, so I was just surprised by it not working with
postgres/pypgsql.

I'm all set, thanks everyone!

-Bob