Re: Outer joins and NULLs (old subject "ERROR: failed to find conversion function from key_vals_nn to record[]")

Bryn Llewellyn <bryn(at)yugabyte(dot)com> writes:
> Self-evidently, a view does *not* inherit constraints from the columns of its base table(s).

Check.

> A view on a single table doesn't necessarily inherit the data types of its base table's columns. Rather, the view compilation's analysis is *sometimes* clever enough to notice when a projected column might have a NULL even when the base column doesn't allow NULLs. In this case, if the base column's data type is (in my example) the domain "text_nn", then the corresponding column in the view is given the data type plain "text". My test that uses a single table shows this.

This is nonsense. The parser does not account for domain constraints in
that way. It would be incorrect to do so, because then the view's data
types could need to change as a consequence of adding/dropping domain
constraints. I think that your result is actually just an illustration
of the rules in
https://www.postgresql.org/docs/current/typeconv-union-case.html
about how the output type of a CASE expression is determined ---
specifically, that domains are smashed to base types as soon as
the CASE arms are discovered to not be all of the same type.

regards, tom lane