Whatever the reason is, but sometimes we decided to change column type of table. It’s quite easy to implement the patch it MySQL, but not in PostgreSQL (I’m currently using pgsql 8.14).

There are two ways (I just know those two) to solve that problem:

1st solution:BEGIN;ALTER TABLE table_name ADD COLUMN new_col new_data_type;

UPDATE table_name SET new_col = CAST(old_col AS new_data_type);

ALTER TABLE table_name DROP COLUMN old_col;

COMMIT;

However, It is used when we want to change type of the latest column (its position in the table), except we can determine to add column in the certain position.

Since it cannot be used in all situations, I have found another way to solve that problem:

2nd solution:1) Copy old table to temporary tableCREATE TABLE temp AS SELECT * FROM old_table;2) Drop old table

DROP TABLE old_table;

3) Create new table (as required structure)

CREATE TABLE new_table ( col1 type(xx), col2 type(xx) );

4) insert data dari table temporary ke table baru

INSERT INTO new_table SELECT * FROM temp ;

Yeah.. that’s it! I think it will be useful.

Popularity: 100% [?]