How can I migrate existing model (includes inserted rows) from nullable column to not nullable

Question

I created a model with column definition

create_table :admin_authentication_tokens do |t|
    t.integer :authentication_ttl
end

And now I want to make the authentication_ttl not nullable. I tried with this migration

change_column :admin_authentication_tokens, :authentication_ttl, :integer, null: false, default: 0

But got an error PG::NotNullViolation: ERROR: column "authentication_ttl" contains null values I understand what the error means but I don't want to migrate the model by manually. So please give me a way to migrate the model with migration file.

Answer 1

You cannot add a NOT NULL constraint to a column that contains null values.

One way or another, you need to get rid of these values before performing such a migration.

For example, as you suggested above, perhaps these should all be set to 0:

MyModel.where(authentication_ttl: nil).update_all(authentication_ttl: 0)

...Or maybe there's some other value that (some of) these columns could be set to?

Or maybe these rows don't actually make sense, and should be deleted?

MyModel.where(authentication_ttl: nil).delete_all

(Or perhaps you should destroy_all, to trigger rails callbacks?)

In short, the answer really depends on your specific circumstances. But one way or another, you need to get rid of the nulls first.

Note that if you have a production environment as well as a local one, then this needs to be taken into consideration before deployment. Again, one way or another (e.g. perhaps via a rake task), you'd need to "fix" the production data before deploying such a migration.