Re: PostgreSQL domains and NOT NULL constraint

On Mon, 23 Oct 2023 at 13:40, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > Given the exception the spec makes for CAST, I wonder if we shouldn't
> > just say "NULL is a valid value of every domain type, as well as every
> > base type. If you don't like it, too bad; write a separate NOT NULL
> > constraint for your table column."
>
> After ruminating on this for awhile, here's a straw-man proposal:
>

[....]

> 3. Left unsaid here is whether we should treat assignments to,
> e.g., plpgsql variables as acting like assignments to table
> columns. I'm inclined not to, because
>
> (3A) I'm lazy, and I'm also worried that we'd miss places where
> this arguably should happen.
>
> (3B) I don't think the SQL spec contemplates any such thing
> happening.
>
> (3C) Not doing that means we have a pretty consistent view of
> what the semantics are for "values in flight" within a query.
> Anything that's not stored in a table is "in flight" and so
> can be NULL.
>
> (3D) Again, if you don't like it, there's already ways to attach
> a separate NOT NULL constraint to plpgsql variables.
>
>
> Documenting this in an intelligible fashion might be tricky,
> but explaining the exact spec-mandated behavior wouldn't be
> much fun either.

This sounds pretty good.

I'd be OK with only running the CHECK clause on non-NULL values. This would
imply that "CHECK (VALUE NOT NULL)" would have exactly the same effect as
"CHECK (TRUE)" (i.e., no effect). This might seem insane but it avoids a
special case and in any event if somebody wants the NOT NULL behaviour,
they can get it by specifying NOT NULL in the CREATE DOMAIN command.

Then domain CHECK constraints are checked anytime a non-NULL value is
turned into a domain value, and NOT NULL ones are checked only when storing
to a table. CHECK constraints would be like STRICT functions; if the input
is NULL, the implementation is not run and the result is NULL (which for a
CHECK means accept the input).

Whether I actually think the above is a good idea would require me to read
carefully the relevant section of the SQL spec. If it agrees that CHECK ()
is for testing non-NULL values and NOT NULL is for saying that columns of
actual tables can't be NULL, then I would probably agree with my own idea,
otherwise perhaps not depending on exactly what it said.

Some possible documentation wording to consider for the CREATE DOMAIN page:

Under "NOT NULL": "Table columns whose data type is this domain may not be
NULL, exactly as if NOT NULL had been given in the column specification."

Under "NULL": "This is a noise word indicating the default, which is that
the domain does not restrict NULL from occurring in table columns whose
data type is this domain."

Under "CHECK (expression)", replacing the first sentence: "CHECK clauses
specify integrity constraints or tests which non-NULL values of the domain
must satisfy; NULLs are never checked by domain CHECK clauses. To use a
domain to prevent a NULL from occurring in a table column, use the NOT NULL
clause."

Also, where it says "Expressions evaluating to TRUE or UNKNOWN succeed": Do
we really mean "Expressions evaluating to TRUE or NULL succeed"?

It would be nice if we had universally agreed terminology so that we would
have one word for the non-NULL things of various data types, and another
word for the possibly NULL things that might occur in variable or column.

If we decide we do want "CHECK (VALUE NOT NULL)" to work, then I wonder if
we could pass NULL to the constraint at CREATE DOMAIN time, and if it
returns FALSE, do exactly what we would have done (set pg_type.typnotnull)
if an actual NOT NULL clause had been specified? Then when actually
processing domain constraints during a query, we could use the above
procedure. I'm thinking about more complicated constraints that evaluate to
FALSE for NULL but which are not simply "CHECK (VALUE NOT NULL)".

Is it an error to specify both NULL and NOT NULL? What about CHECK (VALUE
NOT NULL) and NULL?