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?

You can't. http://stackoverflow.com/questions/17101918/alter-column-data-type-in-amazon-redshift — Philᵀᴹ, Apr 20 '17 at 10:22
http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html doesn't mention a possibility. And that's the official doc. — dezso, Apr 20 '17 at 16:23

enri · Answer 1 · 2019-07-06T11:57:05.537

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.

score 6 · Answer 2 · answered Mar 10 '19 at 14:47

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

Please see https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-sql-implementated-differently.html I don't believe this will actually work. It didn't for me. — scottalan, Apr 12 '19 at 20:35
actually this works for me every time, do you want to share the use case? I can try and assist. — Arik Gortsunian, Aug 12 '20 at 14:49

score 5 · Answer 3 · edited Aug 28 '17 at 20:42

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;

How to increase column size in Redshift database tables?

3 Answers3