HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition.

For example if I wanted to select a group where at least one row has a fold_change value greater than 4, I would expect this to work:

HAVING ANY(rnaseq.fold_change) > 4

But I get the error


ERROR: syntax error at or near "ANY"

I suspect this is because Postgres requires the constant value to be on the left, but if I do it the other way around I get this error

4 < ANY(rnaseq.fold_change)

ERROR: op ANY/ALL (array) requires array on right side

The only way I can get it to work is using array_agg like this:

4 < ANY(array_agg(rnaseq.fold_change))

But that syntax is fairly confusing compared to the first example and having to manually create an array for each group doesn't sound efficient at all.

Is there a proper way to do HAVING ANY in postgres?

You could use either:

HAVING MAX(rnaseq.fold_change) > 4

or the Postgres aggregate function bool_or:

HAVING bool_or( rnaseq.fold_change > 4 )

StackExchange Database Administrators Q#91126, answer score: 5