14

In oracle, I can:

Alter table table_name
modify column_name datatype;

Is this possible in a redshift database?

PhilTM
32k10 gold badges86 silver badges108 bronze badges
asked Apr 20, 2017 at 10:05
2

3 Answers 3

12

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

answered Mar 10, 2019 at 14:47
2
  • 1
    Please see docs.aws.amazon.com/redshift/latest/dg/… I don't believe this will actually work. It didn't for me. Commented 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. Commented Aug 12, 2020 at 14:49
12

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.

answered Jul 3, 2019 at 15:57
6

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;
answered Aug 28, 2017 at 18:50

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.