Applied. ---------------------------------------------------------------------------
Bruce Momjian wrote: > Tom Lane wrote: > > "Matt Nourse" <matt...@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 <br...@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com [ text/x-diff is unsupported, treating like TEXT/PLAIN ] > 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> > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs