When I want to add a new column with a NOT NULL constraint, I need to specify a DEFAULT to avoid violations. However, I don't always want to keep that DEFAULT; going forward after the initial add, I want an error to occur if there are inserts where this data is missing. So I have to DROP DEFAULT on the column. See this SQL Fiddle for a demonstration:
http://sqlfiddle.com/#!12/58750/1/0. I could leave off the NOT NULL constraint, fill the new column with an UPDATE, and then add the NOT NULL constraint afterwards, but that, in my opinion, seems to be a somewhat messier alternative.
By comparison, if I change data types, I can take advantage of the very useful USING clause to specify how to calculate the new value. As near as I can tell, there is no similar functionality for ADD COLUMN to specify a value (or means of calculating a value) only during the execution of the ALTER. I can understand why that might be the case. Without USING, changing the data type would force the creation of a new column instead in many cases, which is a much bigger hardship and makes the data type changing command far less useful.
Am I missing something, or are the ways I mentioned the only ways to accomplish this with ADD COLUMN? It's true that neither possibility is particularly difficult to implement, but it doesn't seem like I should have to create a constraint I don't want or leave off a constraint I do want to add the column. I suppose in some cases, the fact that "fully creating" the column is non-atomic may be a problem. If I'm correct that this feature is not currently present, would adding it be a reasonable feature request? How would I go about making a feature request? (My apologies if there is a how-to on feature requests somewhere; my searching didn't turn it up.)
Thank you.
