blocks|key|81012|text|正如ALTER+TABLE+documentation中所述，您可以使用以下命令更改VARCHAR列的长度|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|81013|ALTER+TABLE+table_name
{
++++ALTER+COLUMN+column_name+TYPE+new_data_type+
}|code-block|syntax|javascript|81014|对于其他列类型，我能想到的就是添加一个具有正确数据类型的新列，然后将旧列中的所有数据插入到新列中，最后删除旧列。|81015|使用与此类似的代码：|81016|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;|81017|将会有模式更改--新添加的列将是表中的最后一列(这可能是COPY语句的问题，请记住这一点--您可以使用COPY定义列顺序)|81018|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html^0|16|7|2|P|0|0|0|0|0|0|S|4|1F|4|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]]|E|$]]|$1|F|3|G|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|17|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|18|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

As noted in the <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html" rel="noreferrer">ALTER TABLE documentation</a>, you can change length of <code>VARCHAR</code> columns using

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE table_name
{
 ALTER COLUMN column_name TYPE new_data_type 
}
</code></pre>

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:

<pre class="lang-sql prettyprint-override"><code>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;
</code></pre>

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

blocks|key|81045|text|要避免Tomasz提到的模式更改，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|81046|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;|code-block|syntax|javascript|81047|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

to avoid the schema change mentioned by Tomasz:

<pre><code>BEGIN TRANSACTION;

ALTER TABLE &lt;TABLE_NAME&gt; RENAME TO &lt;TABLE_NAME&gt;_OLD;
CREATE TABLE &lt;TABLE_NAME&gt; ( &lt;NEW_COLUMN_DEFINITION&gt; );
INSERT INTO &lt;TABLE_NAME&gt; (&lt;NEW_COLUMN_DEFINITION&gt;)
SELECT &lt;COLUMNS&gt;
FROM &lt;TABLE_NAME&gt;_OLD;
DROP TABLE &lt;TABLE_NAME&gt;_OLD;

END TRANSACTION;
</code></pre>

blocks|key|298331|text|(最近更新)可以在Redshift中更改varchar列的类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|298332|ALTER+COLUMN+column_name+TYPE+new_data_type|code-block|syntax|javascript|298333|示例：|298334|CREATE+TABLE+t1+(c1+varchar(100))

ALTER+TABLE+t1+ALTER+COLUMN+c1+TYPE+varchar(200)|298335|这是文档link|offset|length|298336|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html^0|0|0|0|0|4|4|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

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

<pre><code>ALTER COLUMN column_name TYPE new_data_type
</code></pre>

Example:

<pre><code>CREATE TABLE t1 (c1 varchar(100))

ALTER TABLE t1 ALTER COLUMN c1 TYPE varchar(200)
</code></pre>

Here is the documentation <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html" rel="noreferrer">link</a>

blocks|key|298168|text|ALTER+TABLE+publisher_catalogs+ADD+COLUMN+new_version+integer;

update+publisher_catalogs+set+new_version+=+CAST(version+AS+integer);
ALTER+TABLE+publisher_catalogs+DROP+COLUMN+version+RESTRICT;
ALTER+TABLE+publisher_catalogs+RENAME+new_version+to+version;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|298169|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER TABLE publisher_catalogs ADD COLUMN new_version integer;

update publisher_catalogs set new_version = CAST(version AS integer);
ALTER TABLE publisher_catalogs DROP COLUMN version RESTRICT;
ALTER TABLE publisher_catalogs RENAME new_version to version;
</code></pre>

blocks|key|81114|text|此方法适用于将(大)+int列转换为varchar|type|unstyled|depth|inlineStyleRanges|entityRanges|data|81115|--+Create+a+backup+of+the+original+table
create+table+original_table_backup+as+select+*+from+original_table;

--+Drop+the+original+table,+and+then+recreate+with+new+desired+data+types
drop+table+original_table;

create+table+original_table+(
++col1+bigint,
++col2+varchar(20)+--+changed+from+bigint
);

--+insert+original+entries+back+into+the+new+table
insert+into+original_table+select+*+from+original_table_backup;

--+cleanup
drop+original_table_backup;|code-block|syntax|javascript|81116|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This method works for converting an (big) int column into a varchar

<pre><code>-- Create a backup of the original table
create table original_table_backup as select * from original_table;

-- Drop the original table, and then recreate with new desired data types
drop table original_table;

create table original_table (
 col1 bigint,
 col2 varchar(20) -- changed from bigint
);

-- insert original entries back into the new table
insert into original_table select * from original_table_backup;

-- cleanup
drop original_table_backup;
</code></pre>

blocks|key|81170|text|您可以使用以下语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|81171|ALTER+TABLE+<table+name+--etl_proj_atm.dim_card_type+>
ALTER+COLUMN+<col+name+--card_type>+type+varchar(30)|code-block|syntax|javascript|81172|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use the statements below:
<pre><code>ALTER TABLE &lt;table name --etl_proj_atm.dim_card_type &gt;
ALTER COLUMN &lt;col name --card_type&gt; type varchar(30)
</code></pre>

blocks|key|298262|text|对于在redshift中更新相同的列，这将很好地工作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|298263|UPDATE+table_name+
SET+column_name+=+'new_value'+WHERE+column_name+=+'old_value'|code-block|syntax|javascript|298264|可以通过使用和在where中使用多个子句，以消除对sql的任何混淆|298265|干杯！！|298266|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

for updating the same column in redshift this would work fine

<pre><code>UPDATE table_name 
SET column_name = 'new_value' WHERE column_name = 'old_value'
</code></pre>

you can have multiple clause in where by using and, so as to remove any confusion for sql

cheers!!

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?

Alter column data type in Amazon Redshift

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

如何更改Amazon Redshift数据库中的列数据类型？我无法更改Redshift中的列数据类型；有什么方法可以修改Amazon Redshift中的数据类型吗？

问在Amazon Redshift中更改列数据类型
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Amazon Redshift中更改列数据类型EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Amazon Redshift中更改列数据类型
EN