The three valued logic

I have been written quite a few migrations and reviewed quite a few of them at Anarock Tech. This is one of the most common mistakes I see developers making, it's simple but quite easy to overlook.

Context

Can you spot the issue with this simple migration?

Kudos if you spotted it, don't worry if you didn't. Here the problem is the complete field can have 3 values true, false, and NULL.

The three-valued logic

According to Wikipedia

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the undefined value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

Basically, NULL is not the same as false.

In SQL:

• true AND NULL is NULL (not false)
• false OR NULL is NULL (not false)
• true AND NULL OR false is also NULL

Having NULL values in your boolean column might break down causing a lot of issues, even if you handle it everywhere in your application it's not a good practice and it might break down inadvertently or cause a fresh pair of eyes to be confused.

Apart from this queries like

SELECT * FROM projects WHERE complete = FALSE;

It won't give you the correct results.

The Fix

Fortunately, the fix is extremely simple.

The migration is missing null: false, adding this avoids the field from storing NULL values apart from the usual true and false.

But that's not it! Since we are not allowing Postgres to save the NULL value it's a good practice to provide it with a default value in the cases where we don't specify anything. The default value is mandatory in cases where you are adding a column in a pre-existing table as Postgres doesn't know what to set the column value for the existing projects.

The correct migration should be like this:

To sum up, add a NOT NULL constraint and set a default value for boolean data types to be safe while keeping your data normalized.