Skip to main content

Posts

Showing posts from February, 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 )