number equality to null using case when-CodePudding

In my Postgres database, I'm checking user answers for correctness by checking if two IDs, "user_answered_id" and "expected_answer_id", are equivalent. If the user doesn't provide a "user_answered_id", then we still mark their answer as incorrect.

In Postgres, the following queries

select case when 1 != null then TRUE else FALSE end as test;

select case when 1 = null then TRUE else FALSE end as test;

both result in FALSE. This is true for any number check (e.g., when 2 != null, when 3 != null, ..., etc.

Why doesn't CASE WHEN show TRUE for 1 != null?

Must I put in the check "or is null"? E.g.,

CASE WHEN 
  user_answered_id != expected_answer_id 
  OR user_answered_id IS NULL 
  THEN TRUE 
  ELSE FALSE 
END as user_incorrect_tally

CodePudding user response：

What you are looking for is: IS DISTINCT FROM


select 2 is distinct from  null;
 ?column? 
----------
 t

select 2 is distinct from  1;
 ?column? 
----------
 t

From the docs:

datatype IS DISTINCT FROM datatype → boolean Not equal, treating null as a comparable value.

1 IS DISTINCT FROM NULL → t (rather than NULL)

NULL IS DISTINCT FROM NULL → f (rather than NULL)

CodePudding user response：

Yes, You should check NULL value with is null, And last query you wrote is correct.

I suggest you to read below documents:

https://www.postgresql.org/docs/current/functions-comparison.html

CodePudding user response：

SQL uses three-valued logic: true, false, and null. Null is not false. Null can be thought of as "no value".

Operations on null almost always yield null. So 1 != null is null. 1 = null is null. null = null is null. 5 < null is null. Etc.

To check for null, use is null and is not null.

Back to your query. is not distinct from and is distinct from are like = and != which treat null as a comparable value. So null is distinct from 1 will be true.

select
  user_answered_id is distinct from expected_answer_id as user_incorrect

If you need to convert a null into a different value such as 0 or an empty string, use coalesce.

select
  coalesce(user_answered_text, 'No Answer')

Your column is named "tally", but a tally means a count. If you intend to count a user's true and false answers use count with a filter.

select
  count(user_answered_id) filter (
    where user_answered_id = expected_answer_id
  ) as user_correct_tally,
  -- count ignores null, this will only be the questions they tried to answer
  count(user_answered_id) as user_answered_tally,
  count(user_answered_id) filter (
    where user_answered_is is distinct from expected_answer_id
  ) as user_incorrect_tally