ALTERCOLUMN somecolumn TYPE new_data_type
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
ALTERTABLE ma_tiger ALTERCOLUMN 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:
SELECTCASEWHENtrim($1) SIMILAR TO'[0-9]+'THENCAST(trim($1)ASinteger)ELSENULLEND;
LANGUAGE 'sql' IMMUTABLE STRICT;
Now with the USING syntax, we can solve this annoying issue with this command.
ALTERTABLE ma_tiger ALTERCOLUMN fraddl TYPE integer USING pc_chartoint(fraddl);
Some things aren't castable or the cast is ambiguous so undefined.
For example if you have the letter
'a' or a space ' ', how would you cast it to a number? What number would it be?
That would be a judgement call and would vary depending on what you are doing. Sometimes you'd just want to nullify it and sometimes you really want it to fail because such a thing should never happen and signals questionable data.