Everything you probably never wanted to know about NULLs


NULLs are often the enemy of database sanity.  Lacking a clear mathematical definition, they often become a sort of catch-all for undefined behavior.  This property leads to undexpected behavior in Postgres itself even for experienced PostgreSQL professionals.  Even with 24 years of experience with PostgreSQL I am occasioanlly surprised by NULL behavior.

In this talk we will briefly cover a lot of the standard novice mistakes with NULLs and then dive into more exotic territory.  In particular the presentation will cover a lot of unexpected behavior in NULL handling in composite or row types, and how to reason about NULL handling in PostgreSQL.  Some aspects here are likely to be implementation-specific.


In this presentation you will learn:

  1. Common novice mistakes about NULL handling
  2. The differences between a check(attribute is not null) and a not null on an attribute column
  3. Cases where IS/IS NOT NULL breaks down
  4. How to use null record type handling to make anti-joins faster and easier to reason about
  5. Best practices in constraining NULLs when using composite or row types in fields

Come join us on a mind-bending journey into the heart of the UNKNOWN value.

Ballroom H
Thursday, March 14, 2024 - 14:30 to 15:30