In oracle, I can:
Alter table table_name
modify column_name datatype;
Is this possible in a redshift database?
-
1You can't. stackoverflow.com/questions/17101918/…Philᵀᴹ– Philᵀᴹ2017年04月20日 10:22:51 +00:00Commented Apr 20, 2017 at 10:22
-
1docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html doesn't mention a possibility. And that's the official doc.András Váczi– András Váczi2017年04月20日 16:23:15 +00:00Commented Apr 20, 2017 at 16:23
3 Answers 3
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);
-
1Please see docs.aws.amazon.com/redshift/latest/dg/… I don't believe this will actually work. It didn't for me.scottalan– scottalan2019年04月12日 20:35:11 +00:00Commented Apr 12, 2019 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– Arik Gortsunian2020年08月12日 14:49:58 +00:00Commented Aug 12, 2020 at 14:49
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).
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;