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?