On Fri, 18 Mar 2022 at 22:28, Tom Lane <t...@sss.pgh.pa.us> wrote: > Erwin Brandstetter <brsaw...@gmail.com> writes: > > > https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE > > The manual says: > >> count ( "any" ) → bigint > >> Computes the number of input rows in which the input value is not null. > > > But ROW values or composite types that "are null" are counted, anyway. > > Well, there's nulls and nulls. The SQL "IS NULL" construct is fairly > badly designed IMO, because it considers both a plain NULL and a > row-of-all-NULL-fields to be "null". count(), like just about everything > in Postgres other than "IS NULL", considers only a plain NULL to be null. > > This is discussed somewhere in the manual, but I think it's under IS NULL, > not under all the other places that'd have to be annotated if we decide to > annotate as you're suggesting. (One example is that functions that are > marked STRICT use the tighter interpretation.) > > You could use "COUNT(*) FILTER (WHERE NOT (whatever IS NULL))" if you want > to count values meeting the IS NULL definition. (Buttressing my point > that IS NULL is not well thought out, the obvious "whatever IS NOT NULL" > doesn't work here, because it's not the inverse of "whatever IS NULL".) >
I am aware of the mess, and I feel your pain (and my own). But count(<expression>) is among the most frequently used functions, and hardly any user reading the manual will be aware of the implications. Maybe just: ... in which the input value is not null (does not evaluate to a scalar NULL). To give them a fighting chance. Regards Erwin
