CODEWITHSUNDEEP
postgresql
ab11
ab11

Reputation: 20100

Postgres is it necessary to include two predicates in this where clause?

I would like to write a query which returns all rows where action1 is not 't' (including instances where action1 is null)

This query does not return the null records:

select * 
from actions 
where action1 <> 't'

This query does return the null records, but I'm surprised both predicates are necessary

select *
from event_actions
where action_c2 is null or action_c2 <> 't'

Is there a way to write this query without both predicates?

Upvotes: 2

Views: 114

Answers (1)

George S
George S

Reputation: 2151

You can use the IS DISTINCT FROM construction in the comparison, which treats null as if it is a known value that is different from any non-null value i.e. 

select * 
from actions 
where action1 is distinct from 't'

a is distinct from b is equivalent to 

case 
 when a is null then b is null 
 else a is not null then b is not null and a = b 
end

For reference: https://wiki.postgresql.org/wiki/Is_distinct_from

Upvotes: 1

Related Questions