Stored procedure: how to check if NULL is present in postgresql array or not?

Question

array - user_zoned_ids

IF NULL = ANY(user_zones_ids) THEN        
       Do Something
END IF;

But IF condition is always giving false even if NULL is present in array

user330315 · Accepted Answer · 2019-08-05 09:01:12Z

2

You can't use the ANY operator to check for NULL values. You have to unnest the array and count the number of NULL elements:

if (select count(*) from unnest(user_zones_ids) as t(x) where x is null) > 0 then
   ... do something ...
end if;

answered Aug 5, 2019 at 9:01

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

I wish Postgres introduces array.count(element) someday.

little bit faster expression select exists(select * from unnest(ARRAY[1,2,3, null]) g(v) where v is null);