Forem

Augusts Bautra
Augusts Bautra

Posted on

2 1

The most painful reason NULLs are evil

I keep harping on about doing null: false everywhere, especially for strings and booleans, but sometimes there are sneaky exceptions for number fields, where a default of 0 does not make sense and the values will not be available for a time, some draft records etc.

You have to be extremely careful then because apparently NULLs are not "not equal" to anything. What do I mean?

Consider these User records:

id: 1, age: 20
id: 2, age: 25
id: 3, age: nil
Enter fullscreen mode Exit fullscreen mode

How would you query for all users who are not 20?
where.not(age: 20), right? Sorry to say, but User#3 will be omitted from such queries. 😫

You have two options:

  • denullify the age column (may be impossible)
  • tweak the query to handle the silly null edge-case:
where.not(age: 20).or(where(age: nil))
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (1)

Collapse
 
vpospichal profile image
Vlastimil Pospíchal •

ID3 is maybe 20 years old, maybe not.

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more