Amit Shah <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
