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

"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.

regards, tom lane