public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erwin Brandstetter <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: count() counts ROW values that are NULL
Date: Fri, 18 Mar 2022 23:05:33 +0100
Message-ID: <CAGHENJ4yKo4mDLwjCN-nGqpekWZvb=rW=h+diM9Smwp7rZGqMg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg@mail.gmail.com>
	<[email protected]>

On Fri, 18 Mar 2022 at 22:28, Tom Lane <[email protected]> wrote:

> Erwin Brandstetter <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: count() counts ROW values that are NULL
  In-Reply-To: <CAGHENJ4yKo4mDLwjCN-nGqpekWZvb=rW=h+diM9Smwp7rZGqMg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox