Today I Learned

hashrocket A Hashrocket project

Postgres comparison with null values

When Postgres is running comparisons, any null values will yield null at the end of the comparison. This is because the null is an unknown value that Postgres can't run the comparison against.

Take the following simple example of a users table and query:

create table users (name text, email text);

insert into users (name, email)
  values ('Joe', ''),
  ('Rick', null);
select * from users where email not like '';
--  name |       email
-- ------+--------------------
--  Joe  |
-- (1 row)

You'll notice that the Rick user is not returned in the results.

If you want rows with the null value included in your results, you can coalesce the column to an empty string. This allows Postgres to run the comparison against two known values and return the rows with the null values.

select * from users where coalesce(email, '') not like '';
--  name |       email
-- ------+--------------------
--  Joe  |
--  Rick | ΓΈ
-- (2 rows)
See More #sql TILs
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.