3

In oracle, I can:

Alter table table_name
modify column_name datatype;

Is this possible in a redshift database?

4

In AWS Redshift is now possible to alter ONLY VARCHAR column but under these conditions:

  • You can’t alter a column with compression encodings BYTEDICT, RUNLENGTH, TEXT255, or TEXT32K.
  • You can't decrease the size less than maximum size of existing data.
  • You can't alter columns with default values.
  • You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.
  • You can't alter columns inside a multi-statement block (BEGIN...END).

See the documentation.

3

Increasing column size/type in Redshift database table

No, you can't increase the column size in Redshift without recreating the table.

But if the column is last column in the table you can add new column with required changes and move the data and then old column can be dropped as below.

ALTER TABLE TEST ADD COLUMN COLUMN_NEW VARCHAR(100);
UPDATE TEST SET COLUMN_NEW = COLUMN_OLD;
ALTER TABLE TEST DROP COLUMN COLUMN_OLD;
ALTER TABLE TEST RENAME COLUMN COLUMN_NEW TO COLUMN_OLD;
0

Recently AWS added support for increasing the varchar column size,

Alter a VARCHAR Column To conserve storage, you can define a table initially with VARCHAR columns with the minimum size needed for your current data requirements. If later you need to accommodate longer strings, you can alter the table to increase the size of the column. To protect existing data, you can't decrease column size.

The following example changes the size of the EVENTNAME column to VARCHAR(300).

alter table event alter column eventname type varchar(300);

The following command fails because it attempts to decrease the size of the EVENTNAME column.

alter table event alter column eventname type varchar(100);

link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.