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

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> 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.

I do see a use-case that's not covered by ADD COLUMN ... DEFAULT
but could be covered with USING: when you want to initialize the new
column with data taken from some other existing column(s).

Whether this comes up often enough to justify a new feature isn't
clear. You could get the same effect, for pretty much the same cost,
with
1. ADD COLUMN new_col, not specifying any default;
2. UPDATE ... SET new_col = some expression of other columns;
3. ALTER COLUMN new_col SET DEFAULT, if needed.

If you need to make the column NOT NULL, that could be done after step 3,
but then you'd incur another table scan to verify this constraint.
So a USING clause could save you that extra scan.

But if you add another quantum of complication, namely that the new
column's data has to come from some other table, USING would fail at that;
you're back to having to do it with UPDATE. So it seems like there's
only a pretty narrow window of applicability for this proposed feature.
I'm having a hard time getting excited about it.

regards, tom lane