Postgresql – HAVING ANY in Postgres

aggregatepostgresql

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?

Best Answer

You could use either:

HAVING MAX(rnaseq.fold_change) > 4

or the Postgres aggregate function bool_or:

HAVING bool_or( rnaseq.fold_change > 4 )

Related Solutions

Postgresql – Postgres RIGHT JOIN with custom array

You may do an OUTER JOIN directly with a list of values, like this:

SELECT v.id,tablename.* FROM tablename RIGHT JOIN (values (1),(3),...,(9)) as v(id)
  ON (tablename.field=v.id)

Postgresql – How to do an =ANY(SELECT…query in postgresql

Unfortunately = ANY (array) only works with an array literal on the right hand side, not a sub-select.

You need to "normalize" your de-normalized model, using unnest():

SELECT latitude, longitude 
FROM userloc 
WHERE id IN (SELECT unnest(interested) 
             FROM donedeals 
             WHERE deals_id = 64);

If deals_id is unique in the donedeals table, another option is to "convert" the id on the left side to an array and then use the "is contained by" operator: <@:

SELECT latitude, longitude 
FROM userloc 
WHERE array[id] <@ (SELECT interested 
                    FROM donedeals 
                    WHERE deals_id=64 );

Not sure which one would be faster. You will need to check the execution plan.

Best Answer

Related Solutions

Postgresql – Postgres RIGHT JOIN with custom array

Postgresql – How to do an =ANY(SELECT…query in postgresql

Related Question