Today I Learned

A Hashrocket project

Postgres `null` and `where <VALUE> not in`

Always watch out for null in Postgres. When null sneaks into a result set it may confuse the results of your query.

Without nulls a where in query could look like this:

psql> select 'found it' as c0 where 1 in (1);
    c0
----------
 found it
 (1 row)

For the where in clause a null does not change the results.

psql> select 'found it' as c0 where 1 in (null, 1);
    c0
----------
 found it
(1 row)

The where not in formulation however is sensitive to null. Without a null it looks like this:

psql> select 'found it' as c0 where 17 not in (1);
    c0
----------
 found it
(1 row)

Add in the null and the results can be counterintuitive:

psql> select 'found it' as c0 where 17 not in (1, null);
 c0
----
(0 rows)

Watch out for those nulls!!

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, our series of free PostgreSQL screencasts.