Quick, what’s wrong with this Rails migration?
add_column :users, :admin, :boolean
Yep - it can be null. Your Boolean column is supposed to be only
false. But now you’re in the madness of three-state Booleans: it can be
Why to avoid NULL in Boolean columns
Boolean logic breaks down when dealing with NULLs. This StackOverflow answer goes into detail. For example:
true AND NULLis
true AND NULL OR falseis
Fortunately, it’s easy to fix.
NOT NULL constraint means that you’ll never wonder whether a
value means that the user is not an admin, or whether it was never set.
Let’s add the constraint:
add_column :users, :admin, :boolean, null: false
But now the migration doesn’t run.
Set a default value
NOT NULL constraint means that this migration will fail if we have
existing users, because Postgres doesn’t know what to set the column values to
NULL. We get around this by adding a default value:
add_column :users, :admin, :boolean, null: false, default: false
Now our migration runs, setting all users to be not admins, which is the safest option. Later, we can set specific users to be admins. Now we’re safe and our data is normalized.
For more on the danger of null values, read If You Gaze Into nil, nil Gazes Also Into You. You can also browse our Postgres-related blog posts.