Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 84D699FA0EE
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  4 Dec 2006 23:49:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 45758-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  4 Dec 2006 23:49:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id DC5D49F9398
	for <pgsql-sql@postgresql.org>; Mon,  4 Dec 2006 23:49:13 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kB53n7Fn026731;
	Mon, 4 Dec 2006 22:49:07 -0500 (EST)
To: Michael Glaesemann <grzm@seespotcode.net>
cc: "George Pavlov" <gpavlov@mynewplace.com>, pgsql-sql@postgresql.org
Subject: Re: null values in non-nullable column 
In-reply-to: <A4835168-385E-4687-81CE-3AB29D260DFF@seespotcode.net> 
References: 
 <8C5B026B51B6854CBE88121DBF097A8661E3FC@ehost010-33.exch010.intermedia.net>
	<25943.1165287166@sss.pgh.pa.us>
	<A4835168-385E-4687-81CE-3AB29D260DFF@seespotcode.net>
Comments: In-reply-to Michael Glaesemann <grzm@seespotcode.net>
	message dated "Tue, 05 Dec 2006 12:30:44 +0900"
Date: Mon, 04 Dec 2006 22:49:07 -0500
Message-ID: <26730.1165290547@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/50
X-Sequence-Number: 27006

Michael Glaesemann <grzm@seespotcode.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