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

Tom Lane wrote:
> "Matt Nourse" <matthew(at)nplus1(dot)com(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com