Re: error handling unique key constraint in pgsql

Hi Scott,

Thanks for your reply.

Is there a built in exception in pl/pgsql ( similar to oracle's
|DUP_VAL_ON_INDEX ) that I can use?

I ran into an unique situation a couple of days ago. Procedure that
inserts a new value into unique index column was called at the same
time. I even check if the value exist before inserting, but one of the
instance of the function gave me an unique constraint violation error.

My code looks like this:

select into var * from table;

if not found then
 insert into table
 values (a);
end if;
|
I would imagine the postgres db would inherently handle situation like
this, but it did not.

Thanks.

Dinesh


Scott Marlowe wrote:
> On Tue, Jun 10, 2008 at 10:52 AM, Dinesh <dbhandary@mongonet.net> wrote:
>
>> Hi All,
>>
>> I was wondering if there is an easy way to handle unique key constraint in
>> pgsql. Right now my entire transaction aborts if it tries to insert
>> duplicate keys, but I would like to handle this
>> scenario in such a way that the transaction will not break.
>>
>> Please let me know if you have done something like this. I greatly
>> appreciate your help.
>>
>
> There are a couple of approaches:
>
> process the inserts in a pl/pgsql function  and use the exception
> handling in there to catch them.
>
> Use savepoints.
>
> Both of these use the same underlying mechanisms, and both tend to be
> expensive in terms of overhead.
>
>