From: Richard Huxton <dev@archonet.com>
To: George Pavlov <gpavlov@mynewplace.com>
Cc: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: null values in non-nullable column
Date: Tue, 19 Dec 2006 19:33:44 +0000
Message-ID: <45883E98.7080505@archonet.com> (raw)
In-Reply-To: <8C5B026B51B6854CBE88121DBF097A866A48A9@ehost010-33.exch010.intermedia.net>
References: <8C5B026B51B6854CBE88121DBF097A866A48A9@ehost010-33.exch010.intermedia.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, gpavlov@mynewplace.com, schabi@logix-tt.com
Subject: Re: null values in non-nullable column
In-Reply-To: <45883E98.7080505@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox