Re: null values in non-nullable column - Mailing list pgsql-sql

From Richard Huxton
Subject Re: null values in non-nullable column
Date
Msg-id 45883E98.7080505@archonet.com
Whole thread Raw
In response to Re: null values in non-nullable column  ("George Pavlov" <gpavlov@mynewplace.com>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: "George Pavlov"
Date:
Subject: Re: null values in non-nullable column
Next
From:
Date:
Subject: Desc table