postgres - "generic max" based on value's data type

Question

I'm trying to come up with a postgreSQL snippet which all agg any value by its type.

example:

with ni2 as (
 select 1 as id_, TRUE as chid
 UNION ALL select 2 as id_, FALSE as chid
 UNION ALL select 3 as id_, NULL as chid
)
SELECT
  (CASE when pg_typeof(chid)::text != 'boolean' then max(chid)
   else bool_or(chid)
  end) as generic_max
FROM
  ni2

my question is - why does still fail? I'm getting something like:

db error: ERROR: function max(boolean) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The idea here is dynamically choose the right agg function based on the value data type so, ideally I would not care what the data type of the value being aggregated.

Answer 1

See Expression Evaluation Rules:

Another limitation of the same kind is that a CASE cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in a SELECT list or HAVING clause are considered. For example, the following query can cause a division-by-zero error despite seemingly having protected against it:

SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;

The min() and avg() aggregates are computed concurrently over all the input rows, so if any row has employees equal to zero, the division-by-zero error will occur before there is any opportunity to test the result of min(). Instead, use a WHERE or FILTER clause to prevent problematic input rows from reaching an aggregate function in the first place.

I would recommend reading that entire section of the docs.