24

Since Redshift does not support ALTER COLUMN, I would like to know if it's possible to remove the NOT NULL constraints from columns in Redshift.

shihpeng
  • 5,283
  • 6
  • 37
  • 63

3 Answers3

43

You cannot alter the table.

There is an alternative approach. You can create a new column with NULL constraint. Copy the values from your old column to this new column and then drop the old column.

Something like this:

ALTER TABLE table1 ADD COLUMN somecolumn (definition as per your reqm);
UPDATE table1 SET somecolumn = oldcolumn;
ALTER TABLE table1 DROP COLUMN oldcolumn;
ALTER TABLE table1 RENAME COLUMN somecolumn TO oldcolumn;
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 5
    Do note that it won't work if the column is part of the sort keys. Unfortunately, in such situations, you have to re-create the table. – jpmelanson Mar 25 '16 at 17:39
  • 3
    Do note that column you add goes to the end of the table regardless of where the original was so you may run into issues in scenarios where the order of columns matters. – pavol.kutaj May 13 '21 at 12:22
11

There is no way to change column on Redshift.

I can suggest you to create new column, copy values from old to new column and drop old column.

ALTER TABLE Table1 ADD COLUMN new_column (___correct_column_definition___);
UPDATE Table1 SET new_column = column;
ALTER TABLE Table1 DROP COLUMN column;
ALTER TABLE Table1 RENAME COLUMN new_column TO column;
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • I never saw such similar answers in stackoverflow:) I accepted Tripathi's answer since his answer is posted earlier, hope you won't mind. – shihpeng Apr 13 '15 at 07:06
  • 1
    Really funny, so similar answers. I not saw that while you not commented, just answered and left the question. It's all right, good luck and happy coding. – Stanislovas Kalašnikovas Apr 13 '15 at 07:14
1

The accepted answer can produce an error:

cannot drop table <table_name> column <column_name> because other objects depend on it

Adding CASCADE at the end of the DROP COLUMN statement will fix this, however it can have the unwanted side effect of dropping other tables if they are dependent on it.

ALTER TABLE table1 ADD COLUMN newcolumn (definition as per your reqirements);
UPDATE table1 SET newcolumn = oldcolumn;
ALTER TABLE table1 DROP COLUMN oldcolumn CASCADE;
ALTER TABLE schema_name.table1 RENAME COLUMN newcolumn TO oldcolumn;

I found this information here, when the accepted answer wasn't working for me: https://forums.aws.amazon.com/message.jspa?messageID=463248

Also note: When I tried to rename the column, I got another error: relation does not exist

To fix that, I added the schema name in front of the table name in the RENAME COLUMN statement

binarybelle
  • 71
  • 1
  • 9
  • 2
    If the drop column failed with that error message, then adding `CASCADE` ***will*** have unwanted side effects. The note `Just make sure another object doesn't depend on it first.` is meaningless; if another object doesn't depend on it, there would be no error message... – MatBailie Jan 31 '19 at 19:48