Re: BUG #18451: NULL fails to coerce to string when performing string comparison

On Wed, 1 May 2024 at 10:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Fair, we don’t explicitly document that ‘…’ || NULL yields NULL.  It’s more
> > of “unless otherwise noted doing stuff with null results in unknown/null
> > outcomes”.
>
> Yeah, there's a documentation gap here.  I dug around a little and
> really couldn't find anything anywhere in our SGML docs that explains
> NULL in any detail; we tend to assume that you've already heard of it.
> There are a couple of parenthetical remarks in func.sgml that
> reference the notion that NULL means "unknown", but you'd never
> find those if you were looking for an explanation of what NULL is.
> Perhaps that'd be worth a few paras somewhere, though I'm not
> very sure where's a good place.

What is really generic enough about SQL NULLs to put somewhere
generic? I mean NULL = NULL is NULL rather than true, but NULLs are
treated as equal in DISTINCT and GROUP BY. It seems to me it would be
hard to generically write something about it without referencing
specific situations, and if we have to do that, why don't we just
write it specifically in that location?

I see we already mention that false and NULL don't match in a WHERE
clause in [1], per "(i.e., if the result is false or null)".  Maybe
that could be expanded to elaborate more on 3-way SQL boolean logic
and [2] could be expanded to mention that concatenating a NULL yields
NULL.

David

[1] https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WHERE
[2] https://www.postgresql.org/docs/current/functions-string.html