How to remove not null constraint from a column in Redshift

Once the table in created – Redshift does not support altering column to remove a not-null constraint defined on a column.

Below is the workaround to remove a not-null constraint from an existing column:

Step 1: Add a dummy column with no constraint.
Step 2: Update values in the dummy column
Step 3: Drop the old column (the one with constraint)
Step 4: Rename the dummy column

Sql commands for the above steps to remove not null constraint from column1 of table1 which has integer datatype.

Alter table table1 add column dummy_column integer;
update table1 set dummy_column=column1;
alter table table1 drop column column1;
alter table table1 rename dummy_column to column1;
See also  How to drop a materialized view in Redshift database