How to increase column size in Redshift database tables?

Question

In oracle, I can:

Alter table table_name
modify column_name datatype;

Is this possible in a redshift database?

• Philᵀᴹ: You can't. stackoverflow.com/questions/17101918/…
• András Váczi: docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html doesn't mention a possibility. And that's the official doc.

Answer 1

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

• scottalan: Please see docs.aws.amazon.com/redshift/latest/dg/… I don't believe this will actually work. It didn't for me.
• Arik Gortsunian: actually this works for me every time, do you want to share the use case? I can try and assist.

Answer 2

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.

Answer 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;