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.
>
>