How to add not null constraint to a column in Redshift

You can define a column to not accept null values and provide a default value for the column while creating the table as shown below:

create table table1 
(column1 datatype default 0 not null);

Once the table in created – Redshift does not support altering column to define a default value or not-null constraint on a column.

You can add a not-null constraint on an existing table by following the below workaround:

Step 1: Add a dummy column with not null constraint and a default value.
Step 2: Update values in the dummy column
Step 3: Drop the old column
Step 4: Rename the dummy column

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

Alter table table1 add column dummy_column integer not null default 0;
update table1 set dummy_column=column1;
alter table table1 drop column column1;
alter table table1 rename dummy_column to column1;