Yes, the thread did not seem to go very far. The SQL standard does seem
inconsistent in this area, but that is not an argument for allowing data
constraint violation. Until the standard is modified I think it would be
good for the reputation of the DBMS we all love to come up with a fix...
Even though, as Tom Lane explained, CREATE TABLE AS is not the problem
here, it seems to me that might be the cleanest, least obtrusive place
to add validation. If C.T.A failed at the table creation step because of
the JOIN-produces NULLs that would be an early and decent warning.
Fixing it from the JOIN side (e.g. disallowing NULL-generating JOINs on
NOT NULL domains) seems too strict -- JOINs are mostly used for result
sets that are not materialized and you hardly have the potential for a
problem until they are materialized as a table. Similarly, removing the
domain-ness of the JOIN column strikes me as too drastic and as having
the potential of breaking existing functionality. I am sure I am missing
something, just my two cents...
George