Why aggregate functions in PostgreSQL do not work with boolean data type
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
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