In this episode, I'm going to add a not null constraint to an existing Postgres column.
Postgres Version 9.5
Check out this users table:
We have two columns: a full name and an admin flag.
The Rails app that touches this database expects that these two values will never be null. If they are, computers around the world with burst into flames.
Our Rails devs have accomplished this feat by adding ActiveRecord presence validations on the user model.
So, the values can never be null, right?
While we’ve been talking, a rogue developer has logged into psql, and hacking is underway.
In this psql session, my nulls are skulls.
What does it mean if an admin flag is null? Does that mean they aren’t an admin?
Here we can see why
not null database constraints are important. Even if we
could prevent people from inserting null data directly from SQL, the database
still allows it. Someday other applications will interact with this database
too. And we’ll have to tell each of these apps about the constraint, or they
won’t honor it.
The database should be a single source of truth about what kind of data it will and won’t allow.
Unfortunately, we have existing data, and if we try to just add the constraint, we’ll get this error:
alter table users alter column admin set not null; ERROR: column "admin" contains null values
Right, those null values.
What we need to do first is an
update statement. I’ve chosen a default of
false. Usually you can come up with something that’s better than null.
update users set admin = false where admin is null;
Now, the nulls are gone:
Time to add our migration. We’re also going to add a default that matches our
update statement. This will protect us from bad data.
alter table users alter column admin set not null; alter table users alter column admin set default false;
Now things are looking a lot better:
Rails devs may be wondering how to implement this type of constraint on existing data using ActiveRecord. There are several ways to achieve this. At Hashrocket, we write a lot of ActiveRecord migrations in plain old SQL. That’s something we plan to cover in a future episode.
To sum up, protect your database from nulls early, and you will have better data.
Thanks for watching.
-- Set nulls to skulls \pset null 💀 -- Create users table create table users ( full_name varchar not null, admin boolean ); -- Create users with null values insert into users (full_name, admin) values ('Kenneth Parcell', true); insert into users (full_name) values ('Liz Lemon'); insert into users (full_name) values ('Tracy Jordan');