Re: null values in non-nullable column

Michael Glaesemann <grzm(at)seespotcode(dot)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