Re: skip duplicate key error during inserts

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/22/06 04:39, Jeffrey Webster wrote:
> On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
>>
>> Hello,
>> [snip]
>>
>
>
>
> none of them will be inserted because the first insert is a primary key
>> violation. How can I have postgreSQL not mind about the error and
>> proceed to
>> the next insert. I could send the inserts one at a time but bundling them
>> speeds up the process.
>> Thanks
>>
>>
> I feel your pain... However, there is no way to do this (currently?).  Some
> possible solutions had been suggested some time back by a colleague, but
> they were rejected.  It requires a source code change (and not a simple
> one,
> at that) to implement.
>
> Until a concensus is reached by the primary contributors there is no easy
> answer.
>
> We've resorted to batch loading and parsing error messages to load all data
> between primary key violations.  It's still considerably more efficient
> than
> single inserts.
>
> (we've got the code to accomplish what you're asking about in the attic...
> hopefully something will come of it some day.)

The generalized version of this issue (transaction totally fails on
any error) is extremely painful.

Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
don't do that, and there's a lot of code written in the form:

  INSERT INTO ...
  IF PK-ERROR THEN
     UPDATE
  END-IF.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFO0buS9HxQb37XmcRAughAKCrD6o+ibwr7fclE+wBXnUgX3tNDwCgw8Or
5rGcfhYoAH8giSjwwSqHJe8=
=aczL
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster