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