NOT NULL constraint asking for a default value

Question

Adding a new column to my database with a NOT NULL constraint but Redshift is alerting me that I should define a default value. I thought the point of NOT NULL was to force a definition?

ALTER TABLE users add column
  name varchar(255) NOT NULL
;

Results in:

[Amazon](500310) Invalid operation: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression;

Is there a way to enforce a column must not be null?

Answer 1

NOT NULL is the correct way to ensure a column contains a value. However, because your table already exists, the rows which are already in it won't have a value for your new name column, therefore they would breach the NOT NULL constraint.

By specifying a default value, you are ensuring that any existing rows are assigned that value, thereby complying with your NOT NULL constraint.

Answer 2

If the column is NOT NULL, it has to have a value, right?

Why not add: DEFAULT default_expr