How to add a column with not null in Postgres

It can be a little confusing when you see you aren’t able to add a not null constraint to an existing table. Here’s one solution to get around that.

Perhaps you’ve seen the error above where Postgres is complaining about trying to alter a table column adding not null. The problem is simple, you already have rows in the table and you’re creating a column that doesn’t allow nulls. Since there isn’t a default value, null would be giving to that column on existing rows, but that violates the not null constraint. You could add a default value to stop the error, but that doesn’t actually solve the root of the problem.

One solution is just to avoid using not null enough to add the column, then after assigning each row a proper value, you could add the not null constraint. Depending on your use case that might be straightforward or not. For instance, that might mean if you’re adding an email column, that you’ll need to inform your users to set an email address. After sufficient rows have been updated you can add the constraint

Never miss another post!

Get my latest articles delivered directly to your inbox.

Never miss another post!

Get my latest articles delivered directly to your inbox.

🙏

Great Choice!

Thanks for enabling notifications! Don’t worry, I hate spam too and I won’t ever disclose your contact information to 3rd parties.