Re: PostgreSQL domains and NOT NULL constraint
| От | Isaac Morland |
|---|---|
| Тема | Re: PostgreSQL domains and NOT NULL constraint |
| Дата | |
| Msg-id | CAMsGm5eGDEQgGhMnD9jKJOLauH4_YYOEj2=ihNxQRqipEUMGLw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: PostgreSQL domains and NOT NULL constraint (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: PostgreSQL domains and NOT NULL constraint
(Vik Fearing <vik@postgresfriends.org>)
Re: PostgreSQL domains and NOT NULL constraint (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
On Mon, 23 Oct 2023 at 13:40, Tom Lane <tgl@sss.pgh.pa.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?
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Jacob ChampionДата:
Сообщение: Re: pg_dump needs SELECT privileges on irrelevant extension table