Re: Add a NOT NULL column with default only during add

On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>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.