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
