Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 151809FA2CB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  3 Dec 2006 18:23:00 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 90482-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  3 Dec 2006 18:22:48 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 33FD99FA2A6
	for <pgsql-sql@postgresql.org>; Sun,  3 Dec 2006 18:22:51 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kB3MMnqN024733;
	Sun, 3 Dec 2006 17:22:49 -0500 (EST)
To: Amit Shah <ashah@opuspro.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Query for block updates 
In-reply-to: <200612031719.44642.ashah@opuspro.com> 
References: <200612031719.44642.ashah@opuspro.com>
Comments: In-reply-to Amit Shah <ashah@opuspro.com>
	message dated "Sun, 03 Dec 2006 17:19:44 -0500"
Date: Sun, 03 Dec 2006 17:22:49 -0500
Message-ID: <24732.1165184569@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/39
X-Sequence-Number: 26995

Amit Shah <ashah@opuspro.com> writes:
> I am using this query - alter table optiondata ALTER volume TYPE numeric(20,4) 
> using cast(volume as numeric(20,4)); then wait for DB to complain, and then 
> update that value to null, and just keep doing this till all garbadge is 
> removed :-(

You could automate it in plpgsql ... something like

	for r in select id, val from table
	loop
		begin
			perform cast(r.val as numeric(20,4));
		exception
			when ... then
				update table set val = null where id = r.id;
		end;
	end loop;

and then do the ALTER TYPE after you've cleaned the data.

			regards, tom lane