Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 588B79FA2A6 for ; Sun, 3 Dec 2006 18:18:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 91489-03 for ; Sun, 3 Dec 2006 18:18:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mta2.srv.hcvlny.cv.net (mta2.srv.hcvlny.cv.net [167.206.4.197]) by postgresql.org (Postfix) with ESMTP id B26BA9F9F7B for ; Sun, 3 Dec 2006 18:18:21 -0400 (AST) Received: from [192.168.2.12] (ool-44c5e2de.dyn.optonline.net [68.197.226.222]) by mta2.srv.hcvlny.cv.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTP id <0J9P00BHFXYJWIP1@mta2.srv.hcvlny.cv.net> for pgsql-sql@postgresql.org; Sun, 03 Dec 2006 17:18:19 -0500 (EST) Date: Sun, 03 Dec 2006 17:19:44 -0500 From: Amit Shah Subject: Query for block updates To: pgsql-sql@postgresql.org Message-id: <200612031719.44642.ashah@opuspro.com> Organization: MG MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Content-disposition: inline User-Agent: KMail/1.8.2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/38 X-Sequence-Number: 26994 Hi, If this question doesn't belong to this mailing list, please pardon. I have a table with a few hundred million rows, and I need to write a query that does something like follows -- Change all the values of a column to null where the value cannot be casted to numeric(20,4) I have it as varchar, and I need to convert it to numeric(20,4). 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 :-( Thanks, Amit