CODEWITHSUNDEEP
pythonpostgresqlnullwhere-clause
Greem666
Greem666

Reputation: 949

PostgreSQL WHERE and null

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).

Upvotes: 0

Views: 240

Answers (1)

Jasen
Jasen

Reputation: 12422

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 =

Upvotes: 1

Related Questions