MIME-Version: 1.0
From: Erwin Brandstetter <brsaweda@gmail.com>
Date: Fri, 18 Mar 2022 21:58:15 +0100
Message-ID: 
 <CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg@mail.gmail.com>
Subject: count() counts ROW values that are NULL
To: pgsql-docs <pgsql-docs@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005d729105da846911"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg%40mail.gmail.com>
Precedence: bulk

--0000000000005d729105da846911
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-=
AGGREGATE-TABLE

The manual says:

count ( "any" ) =E2=86=92 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. See:

*db<>fiddle [here](
https://dbfiddle.uk/?rdbms=3Dpostgres_14&fiddle=3D7364f3f582322ac687b39c677=
826a074)*

Feels like a bug, but I cannot imagine how this would have slipped
everybody's attention for so long. It should at least be documented. Maybe:

Computes the number of input rows in which the input value is not a plain
NULL value. (Composite or ROW values count in any case - even if value IS
NULL evaluates to true.)

Regards
Erwin

--0000000000005d729105da846911
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><a href=3D"https://www.postgresql.org/docs/curre=
nt/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE">https://www.postgres=
ql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE</a><=
/div><div><br></div></div><div>The manual says:<br></div><br><div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><p class=3D"gmail-func_signature">=
<code class=3D"gmail-function">count</code> ( <code class=3D"gmail-type">&q=
uot;any&quot;</code> ) =E2=86=92 <code class=3D"gmail-returnvalue">bigint</=
code></p><p>Computes the number of input rows in which the input value is n=
ot null.</p></blockquote><br></div><div>
              But ROW values or composite types that &quot;are null&quot; a=
re counted, anyway. See:</div><div><br></div><div>*db&lt;&gt;fiddle [here](=
<a href=3D"https://dbfiddle.uk/?rdbms=3Dpostgres_14&amp;fiddle=3D7364f3f582=
322ac687b39c677826a074)*">https://dbfiddle.uk/?rdbms=3Dpostgres_14&amp;fidd=
le=3D7364f3f582322ac687b39c677826a074)*</a></div><div><br></div><div>Feels =
like a bug, but I cannot imagine how this would have slipped everybody&#39;=
s attention for so long. It should at least be documented. Maybe:<br></div>=
<div><br></div><div>Computes the number of input rows in which the input va=
lue is not a plain NULL value. (Composite or ROW values count in any case -=
 even if <span style=3D"font-family:monospace">value IS NULL</span> evaluat=
es to <span style=3D"font-family:monospace">true</span>.)</div><div><br></d=
iv><div>Regards</div><div>Erwin<br></div></div>

--0000000000005d729105da846911--