What is the Postgres "All" operator?

August 29, 2022

I was reading a query that had the all keyword within a function call:

select count(all 97);
┌───────────┐
│ count(97) │
╞═══════════╡
│         1 │
└───────────┘
Elapsed: 11 ms

What does all (outside a subselect) do in postgres? I was having a hard time finding it in the documentation.

>Solution :

ALL is a "set quantifier" as well as DISTINCT for aggregated functions. It’s defined in section 6.5 of the SQL Standard SQL-92.

It means that all values need to be considered — as in a multiset — and not only distinct values — as in a set. It’s the default behavior is no quantifier is specified.

Excerpt from SQL-92:

6.5  <set function specification>

...

<general set function> ::=
  <set function type>
  <left paren> [ <set quantifier> ] <value expression> <right paren>

<set function type> ::= AVG | MAX | MIN | SUM | COUNT

<set quantifier> ::= DISTINCT | ALL


Syntax Rules

1) If <set quantifier> is not specified, then ALL is implicit.

...