Why aggregate functions in PostgreSQL do not work with boolean data type Why aggregate functions in PostgreSQL do not work with boolean data type postgresql postgresql

Why aggregate functions in PostgreSQL do not work with boolean data type


Because by definition TRUE equals 1 and FALSE equals 0 I do not understand why casting is necessary.

Per the docs you have quoted in your question, a boolean is not, by definition, 1 for TRUE and 0 for FALSE. It's not true in C either, where TRUE is anything non-zero.

For that matter, nor is it for languages that mimic C in this respect, of which there are many. Nor is it for languages such as Ruby, where anything non-Nil/non-False evaluates to True, including zero and empty strings. Nor is it for POSIX shell and variations thereof, where testing a return code yields TRUE if it is zero, and FALSE for anything non-zero.

Point is, a boolean is a boolean, with all sorts of colorful implementation details from a platform to the next; not an integer.

It's unclear how you were expecting Postgres to average true/false values. I'm suspicious that many if any platform will yield a result for that.

Even summing booleans is awkward: would expecting Postgres to OR the input values, or to count TRUE values?

At any rate, there are some boolean aggregate functions, namely bool_or() and bool_and(). These replace the more standard any() and some(). The reason Postgres deviates from the standard here is due to potential ambiguity. Per the docs:

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Here ANY can be considered either as introducing a subquery, or as being an aggregate function, if the subquery returns one row with a Boolean value.

http://www.postgresql.org/docs/current/static/functions-aggregate.html


Here are some possibilities

select max(c::int)::boolean, min(c::int)::boolean, bool_or(c) as max_b,bool_and(c) as min_b from(        select false as c  union select true  union select null) t


Here is how one can achieve max(boolean)

CREATE AGGREGATE max(boolean) (  SFUNC=boolor_statefunc,  STYPE=bool,  SORTOP=">");  

where "boolor_statefunc" is built in function