Can ANY check for IS NOT NULL for an Array in PostgreSQL?

Question

I have a query like this to group by one column and check if any of the records have data in a different column:

SELECT
  bar,
  MAX(CASE WHEN baz IS NOT NULL THEN 1 ELSE 0 END)::BOOLEAN as baz_has_data
FROM
  foos
GROUP BY
  bar

I feel like that's a little bit cryptic, and thought using ANY and ARRAY_AGG would be clearer. Unfortunately I can't find any examples that include both an IS NOT NULL and an ANY(ARRAY_AGG ...). Is this possible?

Erwin Brandstetter · Accepted Answer · 2022-03-29 04:29:12Z

10

There is a dedicated aggregate function for that: bool_or():

SELECT bar, bool_or(baz IS NOT NULL) AS baz_has_data
FROM   foos
GROUP  BY 1;

Yields TRUE if at least one row in the group has a non-null value.

edited Mar 29, 2022 at 4:29

answered Apr 9, 2014 at 21:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Can ANY check for IS NOT NULL for an Array in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related