How to Alter Redshift Table column Data type? Explanation

Amazon Redshift handles petabytes of data without any resource contention. Redshift support many data types to store data into Redshift table. As of now, you can alter Redshift table to increase the size of varchar column. However, Amazon Redshift does not support alter Redshift table column to a different data type for now. This feature may be added in later release for now we will check the alternative to change redshift table column data type.

How to Alter Redshift Table column Data type?

Currently, there is no way to change Redshift column data type. The work around is to create add new column with the correct data type, update that column with data from old column and drop old column.

Read:

• Amazon Redshift Data Types and Best Practices

Following is the example shows how to change Redshift table column data type.

create table sample 
(
id int,
name varchar(100),
age char(10)
);

select * from sample;

alter table sample add column age_new int;

update sample 
set age_new = cast(age as int);

alter table sample drop column age;

alter table sample rename column age_new to age;

describe sample;

There is another way to change redshift table column data type using intermediate table. In this approach, there will be a change in the table schema. The newly added column will be last in the tables. This might cause problem if you are loading the data into this table using Redshift COPY command.

Alter Redshift Table column Data type using Intermediate Table

To avoid the schema change mentioned above, you can use the intermediate table to add new column with correct data type.

Following is the example to modify the redshift table column data type by using intermediate table:

alter table sample rename to sample_old;

create table sample 
(
id int,
name varchar(100),
age int
);

insert into sample select * from sample_old;

drop table sample_old;

Another drawback of this approach is, views which are built on top of table will be become invalid. You have to re-build those views again once the table is re-created.

Read:

• Amazon Redshift Architecture
• Redshift Analytic Functions and Examples
• Amazon Redshift Distribution Types and Examples

Hope this helps 🙂