Today I Learned

A Hashrocket project

Equality comparison and null in postgres

null is weird in postgres. Sure, it’s a way of saying that there is no data. But if there is a null value Postgres doesn’t want to be responsible for filtering the null value unless you explicitly tell it to.

psql> select 1 where null;
 ?column?
----------
(0 rows)

Comparing null to null with = returns null, not true.

psql> select 1 where null = null;
 ?column?
----------
(0 rows)

And comparing a value to null returns neither true nor false, but null.

psql> select 1 where 17 != null or 17 = null;
 ?column?
----------
(0 rows)

So when we apply a comparison to a nullable column over many rows, we have to be cognisant that null rows will not be included.

psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1;
 y
---
 2
(1 row)

To include the rows which have null values we have to explicitly ask for them with is null.

psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1 or x.y is null;
 y
---
 ø
 2
(2 rows)
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.