DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=dOPCgMYvczJuMFWADSc+A+5F750P57F5rX0+8j+/hee5EJNHPo5UXC8tX04rND4d1X
	rv9SG63a2RFLc3ArzMIqmhgBDSpwaXuCM6A4K9oFIneEZj3/jmpYyJF4/5Xy842UW9rQ
	LFKGRB2QfJk6pf8X9mgL2a3YwL1yYs8EX0Vos=
Message-ID: <34d269d40806101438k3fe3833k48ff9e0224b1cbee@mail.gmail.com>
Date: Tue, 10 Jun 2008 15:38:50 -0600
From: "Alex Hunsaker" <badalex@gmail.com>
To: Dinesh <dbhandary@mongonet.net>
Subject: Re: error handling unique key constraint in pgsql
Cc: "Scott Marlowe" <scott.marlowe@gmail.com>, pgsql-admin@postgresql.org
In-Reply-To: <484EE821.6090700@mongonet.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <484EB14B.4010003@mongonet.net>
	<dcc563d10806101209x4edfd77dl28bf514a0742d845@mail.gmail.com>
	<484EE821.6090700@mongonet.net>

On Tue, Jun 10, 2008 at 2:46 PM, Dinesh <dbhandary@mongonet.net> 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.