CODEWITHSUNDEEP
sqlpostgresqlnullbooleanaggregate-functions
user1454117
user1454117

Reputation:

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

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?

Upvotes: 6

Views: 2902

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659137

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.

Upvotes: 10

Related Questions