Re: Wrong result for comparing ROW(...) with IS NOT NULL

Pavel Borisov:
> Sure, it is described here:
> https://www.postgresql.org/docs/13/functions-comparison.html
>
> "If the /|expression|/ is row-valued, then |IS NULL| is true when the
> row expression itself is null or when all the row's fields are null,
> while |IS NOT NULL| is true when the row expression itself is non-null
> and all the row's fields are non-null. Because of this behavior, |IS
> NULL| and |IS NOT NULL| do not always return inverse results for
> row-valued expressions; in particular, a row-valued expression that
> contains both null and non-null fields will return false for both tests.
> In some cases, it may be preferable to write /|row|/|IS DISTINCT FROM
> NULL| or /|row|/|IS NOT DISTINCT FROM NULL|, which will simply check
> whether the overall row value is null without any additional tests on
> the row fields."

Thank you, that explains it very well.

When I realized there was something unexpected going on, I was looking
at all the ROW() syntax in the docs and I found this (as mentioned
upthread):

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

It might be worth it to either add another example for IS NOT NULL, like

SELECT ROW(table.*) IS NOT NULL FROM table; -- detect all-non-null rows

or add a link to section 9.2, that you mentioned. Or do both.

Another thing that could be improved:

Below that example there is a link to section 9.24. The link is:

https://www.postgresql.org/docs/current/functions-comparisons.html

The link you gave me to section 9.2 is (replaced 13 with current):

https://www.postgresql.org/docs/current/functions-comparison.html

Like really? The only difference is the "s" in comparison(s). That
confused me at first for a bit, because I thought I had read your link
already :)

I think that link for 9.24 could be much better chosen.
row-array-comparisons.html would match the content.

Best

Wolfgang