Commented by: @livius2

I see that this is only shortcut to existing aggregates to avoid unnecessary CASE WHEN ..?
Or is here some case which cannot be done already?

Commented by: @mrotteveel

@Karol by that reasoning you don't need most of the aggregate functions. In any case it sounds more like you're thinking of the FILTER-clause, which has already been added as part of CORE5768.

Adding these aggregate functions makes for more readable expressions, or at least, I think attempting to express EVERY using existing aggregate functions will lead to extremely unclear and unreadable expressions.

Commented by: @livius2

No, Mark. I really ask if something cannot be done with CASE WHEN i am simply interested in new things.
I like this sortcuts very much, they simplify things and are usefull, same as FILTER clause.

Commented by: @mrotteveel

@Karol, sorry I misread that :)

For example (using employee database)

select any(emp_no < 4) from employee

is equivalent to

select count(case when emp_no < 4 then 1 end) > 0 from employee

or (using filter clause):

select count(*) filter (where emp_no < 4) > 0 from employee

And

select every(emp_no > 1) from employee

would be

select count(*) = count(case when emp_no > 2 then 1 end) from employee

or (using filter clause):

select count(*) = count(*) filter (where emp_no > 2) from employee

Things get more hairy when filter clauses are used though:

select every(emp_no > 12) filter (where job_code = 'Admin') from employee

is equivalent to something like

select count(*) filter (where job_code = 'Admin') = count(*) filter (where emp_no > 11 and job_code = 'Admin') from employee

Note that PostgreSQL has two aliases BOOL_AND for EVERY and BOOL_OR for ANY/SOME

Commented by: @mrotteveel

The PostgreSQL documentation (https://www.postgresql.org/docs/11/functions-aggregate.html) also remarks on a potential parser ambiguity for ANY/SOME with the quantified comparison predicates:

"""
Boolean aggregates bool_and and bool_or correspond to standard SQL aggregates every and any or some. As for any and some, it seems that there is an ambiguity built into the standard syntax:

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. Thus the standard name cannot be given to these aggregates.
"""

This may be relevant for us as well.