Alter column data type in Amazon Redshift

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?

Solution 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)

Solution 2:

to avoid the schema change mentioned by Tomasz:

BEGIN TRANSACTION;

ALTER TABLE <TABLE_NAME> RENAME TO <TABLE_NAME>_OLD;
CREATE TABLE <TABLE_NAME> ( <NEW_COLUMN_DEFINITION> );
INSERT INTO <TABLE_NAME> (<NEW_COLUMN_DEFINITION>)
SELECT <COLUMNS>
FROM <TABLE_NAME>_OLD;
DROP TABLE <TABLE_NAME>_OLD;

END TRANSACTION;

Solution 3:

(Recent update) It's possible to alter the type for varchar columns in Redshift.

ALTER COLUMN column_name TYPE new_data_type

Example:

CREATE TABLE t1 (c1 varchar(100))

ALTER TABLE t1 ALTER COLUMN c1 TYPE varchar(200)

Here is the documentation link

Solution 4:

If you don't want to change the column order, an option will be creating a temp table, drop & create the new one with desired size and then bulk again the data.

CREATE TEMP TABLE temp_table AS SELECT * FROM original_table;
DROP TABLE original_table;
CREATE TABLE original_table ...
INSERT INTO original_table SELECT * FROM temp_table;

The only problem recreating the table is that you will need to grant again permissions and if the table is too bigger it will take a piece of time.