After you add a column to your table, you can latter *alter* this column to add, change, or remove the default _expression_. There's no need add temporary columns to manage this kind of change. In fact, all of the DDL that you've described can be achieved in one SQL command.
On Thu, Aug 1, 2013 at 3:49 PM, BladeOfLight16 <bladeoflight16@xxxxxxxxx> wrote:
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.
--
Regards,
Richard Broersma Jr.
