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:
- Common novice mistakes about NULL handling
- The differences between a check(attribute is not null) and a not null on an attribute column
- Cases where IS/IS NOT NULL breaks down
- How to use null record type handling to make anti-joins faster and easier to reason about
- 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.