Change {NULL} in Postgres ARRAY to NULL

Question

I have table t with an array column z in Postgres 9.5. I want to select id where z is either NULL OR {NULL}.

id |   z
---+--------
 1 | {NULL} 
 2 |  null     

See DBFIDDLE

I tried changing {NULL} to NULL with array_remove():

SELECT id, 
array_remove(z,NULL) as removed
from t;

Returns:

id |    z   | removed 
---+--------+-------
 1 | {NULL} |   {}      
 2 |  null  |  null

However, if I query this:

select id, z from t where removed is null;

I still get id 1. Ideally, I'd like to avoid unnesting and grouping back up.

Answer 1

To replace an array containing a single NULL element ('{NULL}') with NULL, I suggest NULLIF:

SELECT id, NULLIF(z, '{NULL}') AS z
FROM   t;

db<>fiddle here

'{NULL}' is an (untyped) array literal and the same value as resulting from ARRAY[NULL] - which defaults to the data type text[] without explicit input type or casting.

The above works for any array type: int[], date[], ... because the literal is coerced to the type of z implicitly.

An empty array ('{}') or an array with 1 or more NULL elements ('{NULL}', '{NULL, NULL}', ...) are not the same as NULL. array_remove() is not the right tool.