Changing tables datatype from varchar to text postgressql

Question

I have a problem and search for a solution on Google but can't find any. I have a posgre Tabel with name products_199 and in that table there's a column with name parameter2 type varchar (255). I want to change the datatype to text but somehow I get the following error

ERROR:  parser: parse error at or near "TYPE" at character 50

My command looks like follows that I want to execute

ALTER TABLE products_199 ALTER COLUMN parameter2 TYPE text;

I'm using PostgreSQL 7.3.4

Answer 1

I think that syntax is only available in newer PostgreSQL versions.

If you can't change versions (7.3.4 is quite old), i suggest you just add a new column, copy over the data, and drop the old column. That would be the safest way imo.

Something like this (not tested, backup first!)

begin; 
alter table products_199 add column parameter2_n text; 
update products_199 set parameter2_n=parameter2; 
alter table products_199 drop column parameter2;
commit;
vacuum;