"David G. Johnston" <david.g.johnston@gmail.com> writes: > Or, we can leave it where things are and make sure the reader understands > there are two paths to having a NOT NULL constraint on the newly added > column. Something like:
> "If you plan on having a NOT NULL constraint on the newly added column you > should add it as a column constraint during the ADD COLUMN command. If you > add it later via ALTER COLUMN SET NOT NULL the table will have to be > completely scanned in order to ensure that no null values were inserted."
The first way also requires having a non-null DEFAULT, of course, and then also that default value must be a constant (else you end up with a table rewrite which is even worse). This sort of interaction between features is why I feel that a separate unified discussion is the only reasonable solution.
The paragraph it is being added to discusses the table rewrite already. This does nothing to contradict the fact that a table rewrite might still have to happen.
The goal of this sentence is to tell the user to make sure they don't forget to add the NOT NULL during the column add so that they don't have to incur a future table scan by executing ALTER COLUMN SET NOT NULL.
I am assuming that the user understands when a table rewrite has to happen and that the presence of NOT NULL in the ADD COLUMN doesn't impact that. And if a table rewrite happens that a table scan happens implicitly. Admittedly, this doesn't directly address the original complaint, but by showing how the two commands differ I believe the confusion will go away. SET NOT NULL performs a scan, ADD COLUMN NOT NULL does not; it just might require something worse if the supplied default is volatile.
