Chris,
> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR: Cannot cast type character to integer
>
> I understand this - some tables might have characters in the varchar but
> how to get around it in my case ? I know that my character field has only
> integers in it ?
Actually, you just need to use the to_number function as an intermediary:
UPDATE tab SET new_col = CAST(to_number("OLD_COL", '9999') AS INT);
--
Josh Berkus
Aglio Database Solutions
San Francisco