Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset

Oops. I am very sorry for missing that note in the docs. I agree it does
cover the case I've described quite well.

Tom, please forgive me if this is a non-starter for a suggestion, but what
about forcing a not null constraint on a column that is declared to be of
the type of a domain that has a not null constraint?

Just to be clear, I'm thinking about something like the following:
postgres=>CREATE DOMAIN example TEXT NOT NULL;
CREATE DOMAIN
postgres=>CREATE TABLE usage(myexample EXAMPLE NULL);
ERROR: conflicting NULL/NOT NULL declarations for domain and column
"myexample" of table "usage"
-- I imagine we could also get fancier if it was helpful and do something
like this:
postgres=>CREATE TABLE usage2(myexample EXAMPLE);
NOTICE: NOT NULL constraint from domain added to column "myexample" of
table "usage"

That automatic constraint idea came from my understanding of how PRIMARY
KEY automatically marks the column as NOT NULL. (Oddly, pg allows you to
use both the "NULL" and "PRIMARY KEY" keywords in a column declaration, it
just ignores the NULL.)

This doesn't do anything to fix the underlying issue of being able to
generate a value that violates the domain constraint, but at least it would
prevent the inconsistency of having a table that doesn't allow the insert
of some rows but allows others, and it would prevent users from
inadvertently setting themselves up to allow this.

-Daniel

On Sat, Feb 24, 2018 at 10:35 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Saturday, February 24, 2018, PG Bug reporting form <
> > noreply(at)postgresql(dot)org> wrote:
> >> After some testing, I came across this
> >> unusual behavior that I believe might be a bug.
>
> > This appears to fall within the exceptions for not null noted in the
> > documentation for create domain.
> > https://www.postgresql.org/docs/9.6/static/sql-createdomain.html
>
> Yeah, the core issue here is that at no point does the query attempt to
> convert a value of some other type to the domain type. Rather, the
> appearance of the null result falls out of the semantics of a scalar
> subquery that returns no rows. Like the case for a "not null" domain
> column on the right side of a left join, this is a basic inconsistency
> in the SQL language, and it's not clear that anyone would thank us for
> throwing errors. (But they *would* complain if we slowed down these
> fundamental operations to check for such cases :-(.)
>
> I've occasionally wondered if we should force domain types that appear in
> these contexts to decay to their base type, so as to avoid the situation
> of returning a value that claims to be of a domain type but is actually
> null. (That is, the reported output column type in an outer join or
> whatever would be the base type not the domain type.) I'm doubtful
> that any but pedants would find that to be an improvement, though; and
> I've not come across anything in the SQL standard that suggests an
> implementation should do that.
>
> regards, tom lane
>