On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda <zdravko.balo...@siix.com>wrote:
> Andreas Kretschmer wrote: > >> rverghese <ri...@hotmail.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.
