On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
What you want is a default that only works during ALTER ADD COLUMN. At that point though, there is no data added and DEFAULT only works with INSERTS. Your example of USING with ALTER data_type works because there actually may be rows already existing and you are not creating a column.
Correct me if I'm wrong, but I think you are saying that the use case I have presented is unrealistic. You're saying I would only add a column when there is no data in the table. However, what I'm describing can happen any time you need to make a change to a database with existing data. New features added to an existing application or even simply trying to preserve sample data during development come to mind as situations where you might need to add a NOT NULL column to a table with existing data, so this is a very real situation. The only reason I am bringing up the data type ALTER command is because it already has the feature I'm describing, so I thought it would be a good example of what I'm asking about.
My SQL Fiddle (http://sqlfiddle.com/#!12/58750/1/0) demonstrates what happens when you ADD COLUMN with existing rows already in the table and use a DEFAULT clause; the existing rows are populated with the default value. This is what I want to happen; I am happy with the end result. However, in my opinion, it seems counter intuitive to add a DEFAULT constraint to a column purely to execute the ADD COLUMN, then have to execute a second DDL statement to remove that DEFAULT clause. The command pair is not representative of what I'm actually trying to accomplish, which hurts readability when others might examine my scripts down the line.
So my question is effectively this: Is there an existing, equivalent, single DDL statement to the following hypothetical SQL?
ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL USING 'foo';
where "USING" here would indicate the same thing it does in an ALTER COLUMN data2 TYPE [data type] USING 'foo' command.
I suspect the answer is "No, this feature does not exist right now," and that's fine if so. I am just asking if my guess is correct or if I've missed the feature somewhere. Mr. Broersma's response suggested that this can be done in "one SQL command." I initially took that to mean that there is a single DDL statement that could accomplish this, but having taken a closer look at it, I might have misunderstood.
