Comparison with NULL in PostgreSQL gives unexpected result

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


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)

Tags:

Sql

Postgresql