Hi Arne, thanks for your really good help! :) Martin -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication! On Mon, Aug 02, 2004 at 08:45:21AM +0200, Arne Georg Gleditsch wrote:
* Martin Edenhofer
I need to change the data type of the article.a_message_id from varchar(250) to varchar(1000).
ALTER TABLE article ALTER COLUMN article SET varchar(1000);
is not working, because I think it's not (easy) possible to change the data type in postgrsql.
What is the syntax for postgresql?
Thanks for your help!
You might want to see http://thread.gmane.org/gmane.comp.otrs.devel/530. Essentially, you'll have to do something like this:
ALTER TABLE article ADD COLUMN a_message_id_new varchar(1000); UPDATE article SET a_message_id_new = a_message_id; ALTER TABLE article DROP COLUMN a_message_id; ALTER TABLE article RENAME COLUMN a_message_id_new TO a_message_id;
And then add any necessary default values and keys/constraints to the new column. If this is on a live system, you'll have to lock the table as well, but you can probably do the ADD COLUMN outside the lock. If you want to disturb the live system as little as possible, you probably want to see if renaming the column a_message_id to a_message_id_old is faster than dropping it, and if so do just that inside the transaction and then drop it afterwards.
Arne.