How to convert a table column to another data type

As Robert Treat pointed out in our PostgreSQL 8.3 is out and the Project Moves On, one of the features that was introduced in PostgreSQL 8.0 was the syntax of

ALTER TABLE sometable 
    ALTER COLUMN somecolumn TYPE new_data_type 
    USING some_function_call_to_cast(somecolumn);

The USING syntax is particularly handy because it allows you to control how casts are done. Let us suppose you have a text or varchar field that you realize later on should have been an integer and its padded on top of that because it comes from some stupid DBF or mainframe import.

So now you have this field called - fraddl which is of type CHAR(10). You want to change it to an integer. There are two issues you run into.

If you do something like
```
ALTER TABLE ma_tiger ALTER COLUMN fraddl TYPE integer
```
You get this rather unhelpful message:
column "fraddl" cannot be cast to type "pg_catalog.int4"
Even if the above did work, you've got some stuff in there you don't really care about - letters and so forth or an empty string. So you want to control how the cast is done anyway

To resolve this issue - lets suppose we write a simple function like this which takes a string value and if it looks like a number, it converts it to a number otherwise it just returns NULL:


CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

Now with the USING syntax, we can solve this annoying issue with this command.

ALTER TABLE ma_tiger ALTER COLUMN fraddl TYPE integer USING pc_chartoint(fraddl);