pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feederror handling unique key constraint in pgsql
5+ messages / 3 participants
[nested] [flat]
* error handling unique key constraint in pgsql
@ 2008-06-10 16:52 Dinesh <dbhandary@mongonet.net>
0 siblings, 1 reply; 5+ messages in thread
From: Dinesh @ 2008-06-10 16:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: error handling unique key constraint in pgsql
@ 2008-06-10 19:09 Scott Marlowe <scott.marlowe@gmail.com>
parent: Dinesh <dbhandary@mongonet.net>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2008-06-10 19:09 UTC (permalink / raw)
To: Dinesh <dbhandary@mongonet.net>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: error handling unique key constraint in pgsql
@ 2008-06-10 20:46 Dinesh <dbhandary@mongonet.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dinesh @ 2008-06-10 20:46 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: error handling unique key constraint in pgsql
@ 2008-06-10 21:38 Alex Hunsaker <badalex@gmail.com>
parent: Dinesh <dbhandary@mongonet.net>
0 siblings, 1 reply; 5+ messages in thread
From: Alex Hunsaker @ 2008-06-10 21:38 UTC (permalink / raw)
To: Dinesh <dbhandary@mongonet.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: error handling unique key constraint in pgsql
@ 2008-06-10 21:54 Dinesh <dbhandary@mongonet.net>
parent: Alex Hunsaker <badalex@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dinesh @ 2008-06-10 21:54 UTC (permalink / raw)
To: Alex Hunsaker <badalex@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-10 21:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-10 16:52 error handling unique key constraint in pgsql Dinesh <dbhandary@mongonet.net>
2008-06-10 19:09 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-06-10 20:46 ` Dinesh <dbhandary@mongonet.net>
2008-06-10 21:38 ` Alex Hunsaker <badalex@gmail.com>
2008-06-10 21:54 ` Dinesh <dbhandary@mongonet.net>
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