How to change datatype of a table column?

[gezeala]

Can anybody please me?

Is there a way to change the datatype of a table column..

I don't like the idea of dropping my tables if theres some changes I have to make because if I do this I also have to drop all my views that uses this table column...

[siwis]

create an sql along these lines:

SQL-query:
"ALTER TABLE $tablename CHANGE $fieldname $fieldname TEXT not null"

Substitue the correct datatype where it says TEXT not null
Unless you are changing your field name, $fieldname is repeated twice, elsewise 1st instance should be current fieldname and second instance is the new fieldname.

[chriskl]

MySQL doesn't have views, so he's not using MySQL and hence can't use MySQL syntax.

If you're using Oracle, the syntax is similar. However if you're using Postgres, you're out of luck 🙁 You'll have to dump and reload the table. Look to have this fixed in postgres 7.3 where you can drop column and stuff.

Chris

[gezeala]

btw i'm using postgresQL...

I've already changed the table column datatype ( with the help and guidance of my colleague 'blizz' 🙂 ) by:

1. pgdump databasename > filename.sql

2. Edit filename.sql and change datatype by:
   vi filename.sql

3.createdb newdatabasename

4. psql newdatabasename < filename.sql

outcome is a new db the same with my original db except for the changes I made with the column datatype..

I don't have to drop my tables and views.

thanks anyway...

🙂