Re: Catalog domain not-null constraints

On 3/21/24 15:30, Tom Lane wrote:
> Peter Eisentraut <peter@eisentraut.org> writes:
>> <canofworms>
>> A quick reading of the SQL standard suggests to me that the way we are
>> doing null handling in domain constraints is all wrong.  The standard
>> says that domain constraints are only checked on values that are not
>> null.  So both the handling of constraints using the CHECK syntax is
>> nonstandard and the existence of explicit NOT NULL constraints is an
>> extension.  The CREATE DOMAIN reference page already explains why all of
>> this is a bad idea.  Do we want to document all of that further, or
>> maybe we just want to rip out domain not-null constraints, or at least
>> not add further syntax for it?
>> </canofworms>
> 
> Yeah.  The real problem with domain not null is: how can a column
> that's propagated up through the nullable side of an outer join
> still be considered to belong to such a domain?


Per spec, it is not considered to be so.  The domain only applies to 
table storage and CASTs and gets "forgotten" in a query.


> The SQL spec's answer to that conundrum appears to be "NULL is
> a valid value of every domain, and if you don't like it, tough".


I don't see how you can infer this from the standard at all.


> I'm too lazy to search the archives, but we have had at least one
> previous discussion about how we should adopt the spec's semantics.
> It'd be an absolutely trivial fix in CoerceToDomain (succeed
> immediately if input is NULL), but the question is what to do
> with existing "DOMAIN NOT NULL" DDL.


Here is a semi-random link into a conversation you and I have recently 
had about this: 
https://www.postgresql.org/message-id/a13db59c-c68f-4a30-87a5-177fe135665e%40postgresfriends.org

As also said somewhere in that thread, I think that <cast specification> 
short-cutting a NULL input value without considering the constraints of 
a domain is a bug that needs to be fixed in the standard.
-- 
Vik Fearing