Re: CTAS not honoring NOT NULL, DEFAULT modifiers

Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Actually, SQL:2008 does say that if an output column of the SELECT is
>> known not nullable, then the created table should have the NOT NULL
>> property for that column.  We don't implement anything about "known not
>> nullable", though, so I'd view this as a small part of an unimplemented
>> SQL feature.  The usefulness seems rather debatable anyway.

> It is supposed to inspect the underlying column or look at the data
> values returned and set NOT NULL based on that?  The later seems weird.

"Known not nullable" is entirely different from "all values happen to be
not null at the moment".  I don't recall what conditions the SQL spec
expects people to be able to prove not-nullability from, but being a
direct copy of a NOT NULL column would certainly be the base case.
        regards, tom lane