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

2013-08-02 16:58 keltezéssel, Tom Lane írta:
> Adrian Klaver <adrian.klaver@gmail.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.

If this feature also allows constants and non-volatile functions,
the window isn't so narrow anymore.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/