---
title: Avoid the Three-state Boolean Problem
teaser: |
  Use `null: false, default: false` on boolean columns in
  ActiveRecord migrations to avoid NULL problems.
tags: web,postgresql
author: Gabe Berke-Williams
published_on: 2014-02-24
---

Quick, what's wrong with this Rails migration?

```ruby
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][SO] 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:

```ruby
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:

```ruby
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][gaze]. You can also browse our [Postgres-related blog
posts][postgres posts].

[SO]: http://programmers.stackexchange.com/questions/133600/should-i-store-false-as-null-in-a-boolean-database-field
[gaze]: https://thoughtbot.com/blog/if-you-gaze-into-nil-nil-gazes-also-into-you
[postgres posts]: https://thoughtbot.com/blog/tags/postgres