In Postgresql, after grouping, return false if any value of a column is false. If all values are true/false, then return true/false respectively

Question

I have a table called 'apple' and I wrote the following query:

select name, 
       count(name), 
       case when istasty is null then false else istasty end 
from apple 
group by name, istasty; 

This the output:

I am trying to group the name and istasty flag using following condition:

1. When the corresponding name column has both true or false, then I return false. In the above image, tala has both true and false istasty column. However, I want to return false because it has atleast one false istasty column.
2. If after grouping all istasty column for a particular name column is true then return true; Similarly, if all istasty column is false, then return false for that particular name column.

I can achieve using the bool_and operator in postgresql by writing the query:

select name, count(name), bool_and(coalesce(istasty = true, false)) from apple group by name;

Is there any way I can modify my first query so that I add filter in having clause to achieve the same result as I got using bool_and operator in second query? Or is there any other possible way?

Please note I am not using bool_and operator. I appreciate your time. Thank you.

Answer 1

An alternative to using the bool_and operator would be regular conditional aggregation:

SELECT
    name,
    COUNT(*) AS count,
    CASE WHEN SUM(CASE WHEN !istasty THEN 1 ELSE 0 END) > 0
         THEN FALSE ELSE TRUE END AS result
FROM apple
GROUP BY name;

I am not sure what you have in mind when you suggest using the HAVING clause. By moving the conditional check to the HAVING clause you may exclude/include specific groups in the query matching the condition.

Answer 2

Your logic (return true only if all values are true) equates to getting the min() boolean value:

select
    name,
    min(istasty::varchar(5))::boolean
from (select name, case when istasty is null then false else istasty end istasty
      from apple
      group by name, istasty) x
group by name

Note that postgres doesn't support aggregating boolean values, so you have to cast to character then back to boolean to use min().