Skip site navigation (1) Skip section navigation (2)

Re: Trigger function aborts the transaction on exception

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Rajat Katyal <rajatk(at)intelesoftech(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Trigger function aborts the transaction on exception
Date: 2004-02-28 10:26:33
Message-ID: 20040228102633.GI24860@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
On Sat, Feb 28, 2004 at 03:21:25PM +0530, Rajat Katyal wrote:
> insert into target_table select * from source_table;
> 
> Now there could be scenario that in some record error occured due to
> the typecast of char ---> numeric (e.g if it try to insert
> alphanumeric characters in the numeric datatype ) therefore i want to
> catch the execption and want to make it entry in some other table but
> my process of insertion for the other records should go on as they
> have valid records. But it throws the exception and aborts the
> transaction.

Add a where condition to only add lines that are numbers. For example:

insert into target_table select * from source_table where field ~ '^[0-9.]+$';

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

In response to

pgsql-general by date

Next:From: Benson LeiDate: 2004-02-28 12:47:40
Subject: installation -- help ?
Previous:From: Rajat KatyalDate: 2004-02-28 09:51:25
Subject: Trigger function aborts the transaction on exception

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group