Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 49FA22E0071 for ; Tue, 10 Jun 2008 18:55:14 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68251-06 for ; Tue, 10 Jun 2008 18:55:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mongonet.net (mailgate.mongonet.net [64.157.94.58]) by developer.postgresql.org (Postfix) with ESMTP id E7B5F2E0069 for ; Tue, 10 Jun 2008 18:55:10 -0300 (ADT) Received: from [192.168.1.121] (account dbhandary [192.168.1.121] verified) by mongonet.net (CommuniGate Pro SMTP 5.1.12) with ESMTPSA id 3858003; Tue, 10 Jun 2008 14:55:06 -0700 Message-ID: <484EF82F.8080500@mongonet.net> Date: Tue, 10 Jun 2008 14:54:55 -0700 From: Dinesh User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: Alex Hunsaker CC: Scott Marlowe , pgsql-admin@postgresql.org Subject: Re: error handling unique key constraint in pgsql References: <484EB14B.4010003@mongonet.net> <484EE821.6090700@mongonet.net> <34d269d40806101438k3fe3833k48ff9e0224b1cbee@mail.gmail.com> In-Reply-To: <34d269d40806101438k3fe3833k48ff9e0224b1cbee@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/85 X-Sequence-Number: 29423 Great. That's what I was looking for. Thanks a lot guys. Dinesh Alex Hunsaker wrote: > On Tue, Jun 10, 2008 at 2:46 PM, Dinesh wrote: > >> 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. >> > > > See http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING, > specifically example Example 38-1. > >