Re: error handling unique key constraint in pgsql

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.