Alter column data type in Amazon Redshift

Question

How to alter column data type in Amazon Redshift database?

I am not able to alter the column data type in Redshift; is there any way to modify the data type in Amazon Redshift?

Answer 1

As noted in the ALTER TABLE documentation, you can change length of VARCHAR columns using

ALTER TABLE table_name
{
    ALTER COLUMN column_name TYPE new_data_type 
}

For other column types all I can think of is to add a new column with a correct datatype, then insert all data from old column to a new one, and finally drop the old column.

Use code similar to that:

ALTER TABLE t1 ADD COLUMN new_column ___correct_column_type___;
UPDATE t1 SET new_column = column;
ALTER TABLE t1 DROP COLUMN column;
ALTER TABLE t1 RENAME COLUMN new_column TO column;

There will be a schema change - the newly added column will be last in a table (that may be a problem with COPY statement, keep that in mind - you can define a column order with COPY)