Re: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery

Tom Lane wrote:
> "Matt Nourse" <matthew@nplus1.com.au> writes:
> > CREATE DOMAIN test_id_domain INT NOT NULL;
> > CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value
> > varchar(20) NOT NULL);
> > CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id));
>
> > This produces an error as expected:
>
> > INSERT INTO test_city(state_id) VALUES (NULL);
>
> > This successfully inserts a NULL value into the state_id field:
>
> > INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE
> > display_value = 'Nonexistent state'));
>
> There are any number of ways you can get a similar result, for example
> a LEFT JOIN.  To my mind, this demonstrates why not-null constraints
> associated with datatypes are a fundamentally flawed concept.  If the
> SELECT or LEFT JOIN can produce a null value, as it clearly can, then
> it's nonsensical to think that the output column should be considered
> to be of a NOT NULL domain type.  But what else should it be?  If we
> smash domains to their base types when assigning result types of
> queries, that will make many people unhappy.
>
> Moral: NOT NULL constraints at the domain level suck.  Don't use 'em.

I have written up the following documentation patch to document this
behavior.  It doesn't seem like something we want to fix, so I am not
making it a TODO item.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
Index: doc/src/sgml/ref/create_domain.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v
retrieving revision 1.34
diff -c -c -r1.34 create_domain.sgml
*** doc/src/sgml/ref/create_domain.sgml    3 Apr 2010 07:22:58 -0000    1.34
--- doc/src/sgml/ref/create_domain.sgml    28 May 2010 17:19:35 -0000
***************
*** 121,127 ****
        <term><literal>NOT NULL</></term>
        <listitem>
         <para>
!         Values of this domain are not allowed to be null.
         </para>
        </listitem>
       </varlistentry>
--- 121,132 ----
        <term><literal>NOT NULL</></term>
        <listitem>
         <para>
!         Values of this domain are normally prevented from being null.
!         It is still possible for a domain with this constraint
!         to take a null value if it is assigned a matching domain type
!         that has become null, e.g. via a LEFT OUTER JOIN, or
!         <command>INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM
!         tab WHERE false))</command>.
         </para>
        </listitem>
       </varlistentry>