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

Gordon Linoff · Accepted Answer · 2016-08-05 01:44:36Z

11

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.

answered Aug 5, 2016 at 1:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Craig Ringer · Accepted Answer · 2016-08-05 04:47:40Z

4

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.

answered Aug 5, 2016 at 4:47

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

Postgres not returning rows where value is NULL when !=

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related