July 5, 2020 · Postgres

How to add a column with not null in Postgres

Image: Postgres error column X contains nulls values

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.

Subscribe by Email

Get my latest content straight to your email

    We won't send you spam. Unsubscribe at any time.
    Powered By ConvertKit

    Liked this article? Share it on your favorite platform.

    • LinkedIn
    • Tumblr
    • Reddit
    • Google+
    • Pinterest
    • Pocket
    If you have any questions about this article