Re: ignore unique violation OR check row exists

On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda <zdravko(dot)balorda(at)siix(dot)com>wrote:

> Andreas Kretschmer wrote:
>
>> rverghese <riyav(at)hotmail(dot)com> wrote:
>>
>> I want to insert a bunch of records and not do anything if the record
>>> already
>>> exists. So the 2 options I considered are 1) check if row exists or
>>> insert
>>> and 2) ignore the unique violation on insert if row exists. Any opinions
>>> on whether it is faster to INSERT and then catch the UNIQUE
>>> VIOLATION exception and ignore it in plpgsql versus check if row exists
>>> and
>>> INSERT if it doesn't. I can't seem to ignore the UNIQUE VIOLATION
>>> exception via php, since it is a
>>> plpgsql command, so if I have to do the check and insert, alternatively i
>>> have a function that tries to insert and then ignores the violation. I
>>> was
>>> wondering if one way was better than the other.
>>> Thanks
>>>
>>
>
> Take it out of transaction. Why is there a transaction in the first place?
> If transaction is needed, ok, but take these inserts out and everything
> will
> work as it should. Ignoring UNIQUE VIOLATION or any other error defeats
> the very
> purpose of transaction. That's why you can't ignore it.
>

Unfortunately, bulk inserts are much slower when they don't occur in a
transaction. Try inserting 1 million rows with auto commit enabled vs 1
million rows in 1 transaction, or even 10 or 100 transactions. The
difference is enormous. The bulk insert into an unconstrained table and
then pulling just the new rows over into the destination table in a single
transaction is definitely the most effective way to do this.