Software Architect / Entrepreneur

Software Architect / Entrepreneur

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Perhaps you’ve seen the error above. 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.

Email Notifications

Never miss a post again! Get my latest articles delivered directly to your inbox and be the first to know about my upcoming course!

Email Notifications

Never miss a post again! Get my latest articles delivered directly to your inbox and be the first to know about my upcoming course!

🙏

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.