public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Erwin Brandstetter <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: count() counts ROW values that are NULL
Date: Fri, 18 Mar 2022 17:28:28 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg@mail.gmail.com>
References: <CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg@mail.gmail.com>

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".)

			regards, tom lane

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

* 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