From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: George Pavlov <gpavlov@mynewplace.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: null values in non-nullable column
Date: Mon, 04 Dec 2006 22:49:07 -0500
Message-ID: <26730.1165290547@sss.pgh.pa.us> (raw)
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>
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
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: tgl@sss.pgh.pa.us, grzm@seespotcode.net, gpavlov@mynewplace.com
Subject: Re: null values in non-nullable column
In-Reply-To: <26730.1165290547@sss.pgh.pa.us>
* 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