Comparison with NULL in PostgreSQL gives unexpected result

Question

I'm trying to do a query and stops in source below:

btrim = clean spaces

upper = a -> A

pcc.mac returns "" (null)

pcc.username returns "SIMONE.GISELIA"

btrim(upper(pcc.mac::character varying::text)) <> btrim(upper(pcc.username::text))

if I put the code above, the postgres understand as false, but if I put the code below returns true, why?

SELECT CASE WHEN '' <> 'SIMONE.GISELIA' THEN true ELSE false end

Both fields returns different text and the result is false and shouldn't. (At least I think so)

I'm stuck on that and don't know what do.

I need to compare this options, if don't, the source not work.

If anyone could help me I will be glad for that.

UPDATE WITH CONSOLE SOURCE:

logicasistemas=# SELECT CASE WHEN '' <> 'SIMONE.GISELIA' THEN true ELSE false end;
 case 
------
 t
(1 row)

logicasistemas=# select btrim(upper(pcc.mac::character varying::text)), btrim(upper(pcc.username::text)),
case when ((btrim(upper(pcc.mac::character varying::text))::character varying)::text <> (btrim(upper(pcc.username::text))::character varying)::text) then 1 else 2 end
from provedor_configuracao_cliente pcc
where pcc.cod_servico_contrato = 31905;
 btrim |     btrim      | case 
-------+----------------+------
       | SIMONE.GISELIA |    2
(1 row)

ANOTHER UPDATE:

logicasistemas=# SELECT CASE WHEN null <> 'SIMONE.GISELIA' THEN true ELSE false end;
 case 
------
 f
(1 row)

logicasistemas=# SELECT CASE WHEN null <> 'SIMONE.GISELIA' THEN false ELSE true end;
 case 
------
 t
(1 row)

What??

Answer 1

PostgreSQL distinguishes between the empty string and NULL (unlike varchar in Oracle). Comparing anything to, or calling any function on (unless CALL ON NULL INPUT is set on the function), a NULL value returns NULL.

Your first comparison will be NULL if pcc.mac is NULL, but your second query returns true as the empty string is not equal to 'SIMONE.GISELIA'

> select '' <> 'TEST';
 ?column?
----------
 t
(1 row)

> select btrim(upper(NULL)) <> 'TEST';
 ?column?
----------
 (null)
(1 row)

NULL is not a truth value, that's why your CASE expression is evaluating to 2.

> SELECT CASE WHEN NULL <> 'test' THEN 1 ELSE 2 END;
 case
------
    2
(1 row)

Time: 0.285 ms
> SELECT CASE WHEN '' <> 'test' THEN 1 ELSE 2 END;
 case
------
    1
(1 row)

You can use the coalesce function to return another value when a value is NULL; probably makes sense for you to coalesce to the empty string in your query:

> SELECT coalesce(NULL, '') <> 'TEST';
 ?column?
----------
 t
(1 row)

The IS DISTINCT FROM operator also does what you need - it treats NULL like any other value for comparisons:

> SELECT NULL IS DISTINCT FROM 'SOMETHING';
 ?column?
----------
 t
(1 row)