CODEWITHSUNDEEP
sqlpostgresql
David542
David542

Reputation: 110342

What is the Postgres "All" operator?

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.

Upvotes: 0

Views: 320

Answers (2)

David542
David542

Reputation: 110342

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/current/sql-expressions.html#SYNTAX-AGGREGATES

Upvotes: 0

The Impaler
The Impaler

Reputation: 48840

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.

...

Upvotes: 1

Related Questions