Re: Catalog domain not-null constraints

On 3/21/24 15:30, Tom Lane wrote:
> Peter Eisentraut <peter(at)eisentraut(dot)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