George Pavlov wrote:
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...
I think it has to go in the join...
If a result-set has nulls in a particular column, that column can't be
NOT NULL (by definition). Therefore, either the column has its not-null
constraint removed (through type-casting away the domain) or the query
fails on that NOT NULL constraint.
Any query could result in this sort of problem, not just an explicit
JOIN with NULLs. Imagine a domain "even_numbers_only" and a "SELECT
my_even_numbers+1 FROM foo".
Hmm - it strikes me that any result-set should perhaps have the domain
removed and substituted with its parent type, except perhaps in the
simplest "pass column through" case.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
