Comparing nullable column values using "IS NULL" versus "= NULL"

Question

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

Answer 1

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.

• Victor: Once again! THanks for the info wandering horse!
• Laurenz Albe: Not that it is commendable, but there is a compatibility parameter transform_null_equals that will convert = NULL to IS NULL.
• Victor: 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!
• Laurenz Albe: Use IS NOT DISTINCT FROM like @a_horse_with_no_name recommends.