Thread: Query for block updates

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

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 tableloop    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