Isaac Morland <isaac.morland@gmail.com> writes:
> 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).
Right.
> 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.
The spec doesn't actually allow bare NOT NULL as a domain constraint;
it only has CHECK constraints. Of course you can write CHECK(VALUE
IS NOT NULL), or more-complicated things that will reject a NULL,
but they're effectively ignored during CAST and applied only when
storing to a table column.
I think we decided to implement NOT NULL because it seemed like an
odd wart not to have it if you could do the CHECK equivalent.
In the light of this new understanding, though, I bet they omitted
it deliberately because it'd be too-obviously-inconsistent behavior.
In any case, we can't drop the NOT NULL option now without breaking
apps. I think it should continue to behave exactly the same as
"CHECK(VALUE IS NOT NULL)".
> 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?
Maybe, but then ALTER DOMAIN would have to be prepared to update that
flag when adding or dropping constraints. Perhaps that's better than
checking on-the-fly during DML commands, though.
regards, tom lane