Обсуждение: error handling unique key constraint in pgsql
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. Thanks, Dinesh
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.
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. > >
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.
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 <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. > >