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?

回答1:

There is currently no way to change a column in a redshift database.

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 finaly drop the old column.

Use code similar to that:

ALTER TABLE t1 ADD COLUMN new_column (___correct_column_definition___);
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)

回答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;

回答3:

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.

回答4:

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;

回答5:

Redshift being columnar database doesn't allow you to modify the datatype directly, however below is one approach this will change the column order.

Steps -

1.Alter table add newcolumn to the table 2.Update the newcolumn value with oldcolumn value 3.Alter table to drop the oldcolumn 4.alter table to rename the columnn to oldcolumn

If you don't want to alter the order of the columns then solution would be to

1.create temp table with new column name

2. copy data from old table to new table.

3. drop old table

4. rename the newtable to oldtable

5. One important thing create a new table using like command instead simple create.

回答6:

for updating the same column in redshift this would work fine

UPDATE table_name 
SET column_name = 'new_value' WHERE column_name = 'old_value'

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

cheers!!