postgres column with nulll values and filtering with "!="

Question

Here is my query:

select * from students where status != 4.

I am not getting rows with status null.

Shouldn't it return all the rows where status is not 4 - including rows with null status values?

I can get what I need by following query.

select * from students where status != 4 or status is null

See the manual: postgresql.org/docs/current/static/functions-logical.html - in particularly the truth table there. — Craig Ringer, Commented Jan 19, 2014 at 22:56

user2478690 · Accepted Answer · 2014-01-18 12:40:42Z

4

Use:
IS DISTINCT FROM

SELECT * FROM students WHERE status IS DISTINCT FROM 4;

answered Jan 18, 2014 at 12:40

user2478690

4512 silver badges4 bronze badges

Lukas Eder · Accepted Answer · 2014-01-18 08:04:11Z

2

SQL's understanding of NULL is "three-valued". Think of NULL as UNKNOWN. So, in other words:

UNKNOWN  = 4 yields      UNKNOWN
UNKNOWN != 4 yields also UNKNOWN

In other words, if a student has status is null, then it is "unknown" whether that status is different from 4

answered Jan 18, 2014 at 8:04

Lukas Eder

223k137 gold badges727 silver badges1.6k bronze badges

C.Champagne · Accepted Answer · 2014-01-18 08:05:47Z

0

This is a bit disturbing but null is not a value and requires its specific operator is.

answered Jan 18, 2014 at 8:05

C.Champagne

5,5292 gold badges25 silver badges39 bronze badges

Bohemian · Accepted Answer · 2014-01-18 20:18:09Z

0

Any comparison using normal comparison operators with null is never true, except for the two special IS NULL and IS NOT NULL comparisons.

answered Jan 18, 2014 at 8:04

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

And the other two special comparison operators IS DISTINCT FROM and IS NOT DISTINCT FROM (with their irritating backwards logic).
– mu is too short
Commented Jan 18, 2014 at 19:16

4 Answers 4