Wednesday, February 6, 2008

postgresql altering column type

ever had to change the column type and run into trouble?

well here is what I found as far:

* postgresql 7.4 ( and lower I think ) - cannot be done with the ALTER TABLE command, and then you have to do something like this:

BEGIN;
ALTER TABLE <table name> ADD COLUMN <new column> <new data type>;
UPDATE table SET <new column> = CAST(<old column> AS <new data type>);
ALTER TABLE <table> DROP COLUMN <old column>;
COMMIT;
VACUUM FULL <table>;


VACUUM FULL tab to reclaim the disk space used by the expired rows
( for more detailed information go here)

* postgresql 8.0 ( and higher ) - has implemented the ALTER TABLE ALTER COLUMN command and you have to do something like this:

ALTER TABLE <table name> ALTER COLUMN <column name> TYPE <new type> USING <column name>::<new type>;

( for more detailed information go here)
Post a Comment