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