Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Matt Nourse <matthew(at)nplus1(dot)com(dot)au>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
Date: 2010-05-28 17:20:29
Message-ID: 201005281720.o4SHKTE04325@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Attachment: /pgpatches/domain
Description: text/x-diff (1.1 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2010-05-28 22:31:31
Subject: Re: BUG #5478: ILIKE operator returns wrong result
Previous:From: Tom LaneDate: 2010-05-28 16:50:17
Subject: Re: BUG #5478: ILIKE operator returns wrong result

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group