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', 'joe@hashrocket.com'),
  ('Rick', null);
  
select * from users where email not like '%gmail.com';
--  name |       email
-- ------+--------------------
--  Joe  | joe@hashrocket.com
-- (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 '%gmail.com';
--  name |       email
-- ------+--------------------
--  Joe  | joe@hashrocket.com
--  Rick | ø
-- (2 rows)