Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B3ED5B5DC5E for ; Thu, 22 Sep 2011 03:11:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 13286-07 for ; Thu, 22 Sep 2011 06:11:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mx2-app.tibco.com (mx2-app.tibco.com [63.100.100.143]) by mail.postgresql.org (Postfix) with ESMTP id CA227B5DC13 for ; Thu, 22 Sep 2011 03:11:47 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.68,421,1312182000"; d="scan'208,217";a="27478439" Received: from tibco-5.tibco.com (HELO PA-CASHUB02.na.tibco.com) ([63.100.100.5]) by mx2-app.tibco.com with ESMTP; 21 Sep 2011 23:11:40 -0700 Received: from PA-MBX04.na.tibco.com ([fe80::6418:8781:cd80:4038]) by PA-CASHUB02.na.tibco.com ([fe80::bd2a:d214:3097:6897%11]) with mapi id 14.01.0270.001; Wed, 21 Sep 2011 23:11:39 -0700 From: Amar Dhole To: =?iso-8859-2?Q?Filip_Rembia=B3kowski?= , "pgsql-sql@postgresql.org" Subject: Re: handling duplicate row exception Thread-Topic: [SQL] handling duplicate row exception Thread-Index: AQHMeHL+NUffUIK4f0SOcsatT5qZfZVY6idA Date: Thu, 22 Sep 2011 06:11:39 +0000 Message-ID: References: <1E5EC0E1-13DB-4346-B8EE-212F4DC4CCEA@yahoo.com> In-Reply-To: Accept-Language: en-IN, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.97.98.42] x-tm-as-product-ver: SMEX-10.0.0.1412-6.800.1017-18398.005 x-tm-as-result: No--49.757600-8.000000-31 x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_B056671652AA4D418763981054BCEAB40929EBPAMBX04natibcocom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.403 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/64 X-Sequence-Number: 35864 --_000_B056671652AA4D418763981054BCEAB40929EBPAMBX04natibcocom_ Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hi Filip, No not sure 100% when this can happen. This approach will not be possible a= s in our application we are programmatically handling these cases and going= in other route to add the record with increased key. I am using 9.0.4 version. Thanks Amar ________________________________ From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org= ] On Behalf Of Filip Rembialkowski Sent: 21 September 2011 20:37 To: Amar Dhole Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] handling duplicate row exception Hi There is no IGNORE_DUP_KEY equivalent in PostgreSQL. If you are 100% sure that you want to ignore unique key violations, you can= wrap your INSERT code in PL/PgSQL block and handle the exception yourself. I mean: DO $$ BEGIN INSERT INTO foo (bar,baz) SELECT 42, 666; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'row skipped'; END; $$ BTW - which version of PostgreSQL are you using? Filip 2011/9/21 Amar Dhole > Hi All, I have a requirement where my application tries to enter the duplicate row = in table using batchexceute code. And batch exception is thrown we checked = error code and skip it but after this exception all my next update/insert g= ets error out with following exception "Database error. SQL state 25P02. Database specific error code (if any) was= 0. Database error message (if any) was: org.postgresql.util.PSQLException:= ERROR: current transaction is aborted, commands ignored until end of trans= action block.: Is there any way to proceed ahead like in sql server we have options while= creating table IGNORE_DUP_KEY =3D ON if this is set warning is generated i= nstead of Exception so the other insert/update can proceed ahead. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql --_000_B056671652AA4D418763981054BCEAB40929EBPAMBX04natibcocom_ Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

Hi Filip,

 

No not sure 100% when this can happen.= This approach will not be possible as in our application we are programmat= ically handling these cases and going in other route to add the record with increased key. =

 

I am using 9.0.4 version.

 

Thanks
Amar

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Filip Rembialkowski
Sent: 21 September 2011 20:3= 7
To: Amar Dhole
Cc: pgsql-sql@postgresql.org=
Subject: Re: [SQL] handling = duplicate row exception

 

Hi

There is no IGNORE_DUP_KEY equivalent in PostgreSQL.

If you are 100% sure that you want to ignore unique key violations, you can= wrap your INSERT code in PL/PgSQL block and handle the exception yourself.=

I mean:

DO $$
BEGIN
 INSERT INTO foo (bar,baz) SELECT 42, 666;
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'row skipped';
END;
$$


BTW - which version of PostgreSQL are you using?

Filip


2011/9/21 Amar Dhole <adhole@tibco.com>

Hi All,

I have a requirement where my application tries to enter the duplicate row = in table using batchexceute code. And batch exception is thrown we checked = error code and skip it but after this exception all my next update/insert g= ets error out with following exception

"Database error. SQL state 25P02. Database specific error code (if any= ) was 0. Database error message (if any) was: org.postgresql.util.PSQLExcep= tion: ERROR: current transaction is aborted, commands ignored until end of = transaction block.:


 Is there any way to proceed ahead like in sql server we have options = while creating table IGNORE_DUP_KEY =3D ON if this is set warning is genera= ted instead of Exception so the other insert/update can proceed ahead.



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

 

--_000_B056671652AA4D418763981054BCEAB40929EBPAMBX04natibcocom_--