在Amazon Redshift中更改列数据类型 [英] Alter column data type in Amazon Redshift

问题描述

如何在Amazon Redshift数据库中更改列数据类型?

我无法在Redshift中更改列数据类型；有什么方法可以修改Amazon Redshift中的数据类型?

解决方案

如 ALTER TABLE文档，您可以使用更改VARCHAR列的长度

 ALTER TABLE table_name
{
    ALTER COLUMN column_name TYPE new_data_type 
}
 

对于其他列类型，我能想到的就是添加具有正确数据类型的新列，然后将旧列中的所有数据插入到新列中，最后删除旧列.

使用类似的代码:

 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;
 

将发生模式更改-新添加的列将位于表的最后(这可能与COPY语句有关，请记住这一点-您可以使用COPY定义列顺序)

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?

解决方案

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)

这篇关于在Amazon Redshift中更改列数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！