Rails, ActiveRecord, and PG::NotNullViolation

PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint

If you see this error when migrating a site using ActiveRecord from MySQL to PostgreSQL, here's what to do about it.

First, remember that ActiveRecord magically configures itself from what is running in the database. It’s how it figures out which columns are the magic id columns so vital to the functioning of most applications.

Second, remember that MySQL uses sequences differently from PostgreSQL in determining the next value for an id. When you ported the schema and data from MySQL to PostgreSQL, that piece of logic was not translated.

Putting the previous two thoughts together, we realize that what’s happening is that the newly migrated PostgreSQL database does not contain the logic to auto-increment the id column correctly, and ActiveRecord thus does not realize that the id column should be handled specially, and tries to insert it as a null value.

The solution is three-fold:

1. Alter the id columns across all your tables to auto-increment correctly in PostgreSQL;
2. Alter the sequence to have the correct current value;
3. Restart your application so that ActiveRecord can reload.

If you need more help, see my blog post.