Postgres not returning rows where value is NULL when !=

Question

I see a thread here: Why does PostgreSQL not return null values when the condition is <> true with answers explaining why it happens but I'm still not sure how to fix it.

I'm running a query similar to this:

SELECT * FROM beers WHERE name != 'Budlight';

I expect it to return rows where name is not equal to Budlight. The results should include rows where name is NULL. Instead my results shows rows where name is not Budlight or NULL.

How can I form the query where only rows where name is not Budlight are ommitted from results?

SQLFiddle: http://www.sqlfiddle.com/#!15/7b9bd/1

Answer 1

Postgres offers the is distinct from operator. You can do:

SELECT *
FROM beers
WHERE name IS DISTINCT FROM 'Budlight';

This does what you expect with NULL values.

This operator is explained in the documentation.

Answer 2

The SQL-standard way is just to say OR col IS NULL, e.g.

SELECT * FROM beers WHERE name <> 'Budlight' OR name IS NULL;

Gordon's answer points out IS DISTINCT FROM a non-sql-standard way of saying that. Note that PostgreSQL may not use indexes effectively for IS DISTINCT FROM so I tend to only use it in triggers comparing scalars, and use col <> 'something' OR col IS NULL the rest of the time.