On this page:
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
there is this user comment:
--------------------
To change the data type of a column, do this:
BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab RENAME old_col TO temp_name;
ALTER TABLE tab RENAME new_col TO old_col;
ALTER TABLE tab DROP COLUMN temp_name;
COMMIT;
You might then want to do VACUUM FULL tab to reclaim the disk space
used by the expired rows.
--------------------
The 8.1 release (and the 8.0 release) support the same functionality
with a single line:
ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type;
I think the user comment should be removed, unless there is some
benefit to using the multi-step process. If there is some benefit, I
think it should be described, so that users know when to use it instead
of the simpler technique.
-Kevin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
