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.
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
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.
NULL is not the same as
true AND NULLis NULL (not false)
false OR NULLis NULL (not false)
true AND NULL OR falseis 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.
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
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.