Avoid the Three-state Boolean Problem

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 true or false. But now you’re in the madness of three-state Booleans: it can be true, false, or NULL.

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 NULL is NULL (not false)
• true AND NULL OR false is NULL

Fortunately, it’s easy to fix.

NOT NULL

Adding a NOT NULL constraint means that you’ll never wonder whether a NULL 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

The 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 other than 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.

What’s next

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.