On 3/21/24 15:30, Tom Lane wrote:
Peter Eisentraut <pe...@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
