From: Dinesh <dbhandary@mongonet.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: error handling unique key constraint in pgsql
Date: Tue, 10 Jun 2008 13:46:25 -0700
Message-ID: <484EE821.6090700@mongonet.net> (raw)
In-Reply-To: <dcc563d10806101209x4edfd77dl28bf514a0742d845@mail.gmail.com>
References: <484EB14B.4010003@mongonet.net>
<dcc563d10806101209x4edfd77dl28bf514a0742d845@mail.gmail.com>
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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dbhandary@mongonet.net, scott.marlowe@gmail.com
Subject: Re: error handling unique key constraint in pgsql
In-Reply-To: <484EE821.6090700@mongonet.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox