Adding Not Null to an Existing Column
In this episode, I'm going to add a not null constraint to an existing Postgres column. This episode is brought to you by Hashrocket, a consultancy with expertise in PostgreSQL - visit us at https://hashrocket.com and www.pgcasts.com
Transcript:
Check out this users table:
```sql
\d users;
```
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?
Wrong.
While we've been talking, a rogue developer has logged into psql, and hacking
is underway.
```sql
table users;
```
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:
```sql
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.
```sql
update users set admin = false where admin is null;
```
Now, the nulls are gone:
```sql
table users;
```
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.
```sql
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:
```sql
\d users;
```
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.
Setup
```sql
-- 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');
```
Смотрите видео Adding Not Null to an Existing Column онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь PG Casts by Hashrocket 19 Июль 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 6,782 раз и оно понравилось 23 людям.