0

I'm dealing with nullable values in a column of a table, i'm wondering why this:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 is null

returns a result set with the two "rows" having a null value in c2; comparting to this query:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 = null

that returns an empty result set!!

so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.

P.D: This question also applies to the case is NOT NULL versus <> NULL

3

As documented in the manual it's not possible to use = to test for NULL values:

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL.

is there "null-compatible operator" to use - Yes, right below the above quote it states:

When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM predicates

So you can use

where c2 is not distinct from $1

One drawback of the IS DISTINCT or IS NOT DISTINCT operator is however, that they can't use an index.

  • Once again! THanks for the info wandering horse! – Victor May 29 at 16:46
  • 2
    Not that it is commendable, but there is a compatibility parameter transform_null_equals that will convert = NULL to IS NULL. – Laurenz Albe May 29 at 16:48
  • THanks @LaurenzAlbe, i guess i'm not the first persons facing this problem... so any advise about a proved solution to go on with this, is more than welcome! – Victor May 29 at 16:51
  • 1
    Use IS NOT DISTINCT FROM like @a_horse_with_no_name recommends. – Laurenz Albe May 29 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.