Select rows in postgres table where an array field contains NULL

Question

Our system uses postgres for its database.

We have queries that can select rows from a database table where an array field in the table contains a specific value, e.g.:

Find which employee manages the employee with ID 123.

staff_managed_ids is a postgres array field containing an array of the employees that THIS employee manages.

This query works as expected:

select *
from employees
where 123=any(staff_managed_ids)

We now need to query where an array field contains a postgres NULL. We tried the following query, but it doesn't work:

select *
from employees
where NULL=any(staff_managed_ids)

We know the staff_managed_ids array field contains NULLs from other queries.

Are we using NULL wrongly?

score 2 · Accepted Answer · 2019-10-11 11:53:13Z

2

NULL can not be compared using =. The only operators that work with that are IS NULL and IS NOT NULL.

To check for nulls, you need to unnest the elements:

select e.*
from employees e
where exists (select *
              from unnest(e.staff_managed_ids) as x(staff_id)
              where x.staff_id is null);

edited Oct 11, 2019 at 11:53

answered Oct 11, 2019 at 10:36

user330315

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

Comments

Haleemur Ali · Accepted Answer · 2019-10-11 11:49:35Z

0

if all your id values are positive, you could write something like this:

select *
from employees
where (-1 < all(staff_managed_ids)) is null;

how this works is that -1 should be less than all values, however comparison with null will make the whole array comparison expression null.

edited Oct 11, 2019 at 11:49

answered Oct 11, 2019 at 10:37

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

1 Comment

Artie Leech Over a year ago

Thanks for your answer - although it's more compact, we couldn't guarantee that every existing id would be > -1, so the answer from @a_horse_with_no_name is more apt in our case.

Collectives™ on Stack Overflow

Select rows in postgres table where an array field contains NULL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related