Re: null values in non-nullable column

Michael Glaesemann <grzm@seespotcode.net> writes:
> Could you expand on that a bit? Here's what I've interpreted:

> The column types of the select are assumed to be (int,  
> boolean_not_null), and so the values aren't checked again before the  
> insert during CREATE TABLE AS. "discarding domain-ness" would mean  
> considering the results as their base type, and rechecking the domain  
> would be checked when inserting into the table.

To be clear: the problem is not with CREATE TABLE AS.  The problem is
with the definition of what a SELECT ... LEFT JOIN ... is supposed to
return.  C.T.A. is supposed to create a table matching the defined
column types of the SELECT result; either those types allow null-ness,
or they don't.

Actually I think this is a bug in the SQL spec :-(  The description of
<joined table> says that output columns are "possibly nullable" if
they're on the nullable side of the outer join, but it's not apparent
that that idea is meant to negate a domain constraint.  And yet, if it
does not, then an outer join with a NOT NULL domain column on the
nullable side is just invalid.
        regards, tom lane