Message-ID: <4F042291.6040903@siix.com>
Date: Wed, 04 Jan 2012 10:57:37 +0100
From: Zdravko Balorda <zdravko.balorda@siix.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20110906)
MIME-Version: 1.0
CC: pgsql-sql@postgresql.org
Subject: Re: ignore unique violation OR check row exists
References: <1325624070446-5117916.post@n5.nabble.com>
	<20120104090838.GA6039@tux>
In-Reply-To: <20120104090838.GA6039@tux>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


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.

Zdravko