Two ways to ensure no NULLs in PostgreSQL

I think everyone knows that you can set a column as NOT NULL in the database. It’s part of a standard SQL and nothing to praticularly blabber about. However, PostgreSQL also has a second way to make sure the value in a column is not NULL. This is done via a NON NULL check.

If we were to create a new table, the difference would look like this:

CREATE TABLE products (
  price integer NOT NULL
)

-- vs

CREATE TABLE products (
  price integer CHECK (price IS NOT NULL)
)

The second approach has a few properties: it’s sligtly less efficient, the column shows up as nullable when you describe the table (the check is listed underneath, in table constraints) and it’s not exactly standard SQL.

So why even consider it?

The main upside is that you can create a check constraint as invalid. This means that it will be checked only for new or updated records, but the ALTER statement won’t scan the whole table to check existing records. This is very important on a large tables, because just adding a NOT NULL to an existing column will trigger this whole table scan - and to make things worse, with an ACCESS EXCLUSIVE LOCK. This is absolutely not good, so using a CHECK is a much better option. You can then VALIDATE a check to make sure all rows in the table are correct. The VALIDATE does not take ACCESS EXCLUSIVE LOCK as well.

References

• Fly.io’s guide on safe migrations with Ecto: Setting NOT NULL on an existing column
• Christophe Escobar: Adding a NOT NULL CONSTRAINT on PG Faster with Minimal Locking