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

On 08/02/2013 01:03 AM, BladeOfLight16 wrote:
> On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com
> <mailto: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.

No I am saying that in the ALTER data_type case the column is not being
created and USING is working on data(assuming data had actually been
entered already) that exists. What you propose is a two step process,
create a column and then fill it with a default value that goes away
after the ALTER TABLE ADD COLUMN statement. In fact what you are already
doing.

>
> 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';

As Vik stated, no,

>
> 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.

Not to put words in Richards mouth, but I suspect what he was saying was
to wrap the DDL changes and initial inserts in a single transaction:

BEGIN:
CREATE TABLE x
(
id SERIAL PRIMARY KEY,
data1 VARCHAR(10) NOT NULL
);

INSERT INTO x (data1) VALUES ('hello'), ('world'), ('sunshine');

ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo';

ALTER TABLE x
ALTER COLUMN data2 DROP DEFAULT;

COMMIT;

INSERT INTO x (data1, data2) VALUES ('moonlight', 'baz');

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com