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.