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)

Answer 2

you can use IS NULL or IS NOT NULL to compare two values where one value may have NULL

SELECT VAL1, VAL2 FROM TABLE WHERE VAL1 IS NULL;

SELECT VAL1, VAL2 FROM TABLE WHERE VAL1 IS NOT NULL;

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard
Source : PostgreSQL Comparison Operator : Documentation