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>