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