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.

CodePudding user response：

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 if 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.

...

CodePudding user response：

Seems it's just an explicit way to say 'default behavior' as opposed to doing COUNT(DISTINCT ...). From the docs:

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

The first form of aggregate expression invokes the aggregate once for each input row. The second form is the same as the first, since ALL is the default. The third form invokes the aggregate once for each distinct value of the expression (or distinct set of values, for multiple expressions) found in the input rows. The fourth form invokes the aggregate once for each input row; since no particular input value is specified, it is generally only useful for the count(*) aggregate function. The last form is used with ordered-set aggregate functions, which are described below.

https://www.postgresql.org/docs/9.5/sql-expressions.html#SYNTAX-AGGREGATES