Jan 10 2007
Postgresql: How to change column type
pre { padding: 1em; border: 1px dashed #2f6fab; color: black; background-color: #f9f9e9; line-height: 1.5em; font-size:9pt}
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 table CREATE 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: 4% [?]
Posting yang berhubungan: (otomatis digenerate)
Powered by FireStats
I need change the row from varchar2 to number, but mi row contain , for example
3,000,000.00 and i want 3000000.00
Seems that this feature is available on Oracle using TO_NUMBER() isn’t it? But I’m not sure. How about on Postgresql? I will try which function is available for that.