On Sun, Jun 19, 2022 at 2:31 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
It would be foolish, therefore, to define the target table for "insert-select" using "CTAS where false".
SQL is a strongly typed language where the structure of the query output is determined without any consideration of whether said query returns zero, one, or many rows. Because of this property it is entirely consistent that a CTAS produces a table even if the query execution produces zero rows.
That CTAS chooses to not try and produce constraints on the newly created table by inferring them from the underlying query seems like a reasonable trade-off between functionality and effort. It simply stops at "data types" and doesn't care for how any given one is implemented. That domains have constraints is incidental to the entire design.
Allowing domains to be defined as not null at this point is simply something that we (IMO, the documentation is not this strongly worded) don't support but don't error out upon in the interest of backward compatibility. It, as you note, has some corner-case bugs. You can avoid those bugs by simply not using a non-null constraint as suggested.
As for the "same source" optimization: the documentation reads - "For example, this can happen in an outer-join query,...", the bug-fix here is to simply add this situation as a second example. However, it is reasonably considered correct that a record you just read from a table should be able to be written back to said table unchanged. The "error" is that we allowed the record to exist in the first place, but we absolved ourselves of responsibility with the caveats on the CREATE DOMAIN page. Subsequent consequences of that mis-use are likewise attributed to said mis-use and are on the user's head for allowing their code to produce the problematic behavior.
At most we should probably go from saying "Best practice therefore..." to "We no longer support setting a not null constraint on a domain but will not error in the interest of not breaking existing uses that are careful to avoid the problematic corner-cases".
David J.
