PostgreSQL WHERE and null

Question

I have a table which looks like this:

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

I dont follow. Running query SELECT NULLIF('', '') returns a cell with [null] value for me. Doesnt that satisfy pr = [null] condition? — Greem666
– Greem666, Commented Aug 13, 2018 at 1:46

Jasen · Accepted Answer · 2018-08-13 02:01:02Z

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 =

answered Aug 13, 2018 at 2:01

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL WHERE and null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related