1

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?

3

You could use either:

HAVING MAX(rnaseq.fold_change) > 4

or the Postgres aggregate function bool_or:

HAVING bool_or( rnaseq.fold_change > 4 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.