PostgreSQL WHERE and null

Question

I have a table which looks like this: enter image description here

When I try to look up only the row with case_id = 5 based on pr, sr, sn, I use the following code:

SELECT case_id, dupl_cnt
FROM cases
WHERE pr = NULLIF('', '')::INT AND
 sr = NULLIF('CH_REP13702.10000', '')::VARCHAR AND
 sn = NULLIF('22155203912', '')::VARCHAR

However, the code above does not yield any result (empty query result). I have narrowed it down to being some sort of an issue with the "pr" value being null - when "pr" removed line is removed from the above query, it starts to work as expected. Can someone explain to me why is that happening? I am anticipating pr or sr columns at times to feature NULL values, but still have to be able to look up case_id numbers with them. (NULLIF function is in there because it is a part of Python integration with psycopg2 module, and I have to anticipate that sometimes data entry will feature empty string for these values).

• Paula Thomas: Because NULL is not the same as an empty string?
• Greem666: I dont follow. Running query SELECT NULLIF('', '') returns a cell with [null] value for me. Doesnt that satisfy pr = [null] condition?
• Parfait: What IDE/tool is that table screenshot from?
• Greem666: Parfait: pgAdmin4

Answer 1

NULLIF('', '') returns [null]

that deos'nt that satisfy the pr = [null] condition because

anything = NULL returns NULL

You need to use IS NOT DISTINCT FROM instead of =