Re: skip duplicate key error during inserts

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: skip duplicate key error during inserts
Date: 2006-10-22 10:24:46
Message-ID: 453B46EE.3050001@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

On 10/22/06 04:39, Jeffrey Webster wrote:
> On 10/20/06, Jean-Christophe Roux <jcxxr(at)yahoo(dot)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-----

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-10-22 11:15:09 Re: How to find out about zlib compression
Previous Message Jeffrey Webster 2006-10-22 09:39:18 Re: skip duplicate key error during inserts