Re: Difference between "add column" and "add column" with default

On Mon, Mar 20, 2006 at 10:52:36AM +0100, Guido Neitzer wrote:
> Hi.
>
> What is the technical difference between adding a column to a table
> and then apply a "set value = ..." to all columns and adding a column
> with a default value = ...?

What version are you using:

# alter table a add column b int4 default 0;
ERROR: adding columns with defaults is not implemented

The latter doesn't work in a single step. The former does indeed
duplicate all the rows.

> I have seen that the first duplicates all rows, I had to vacuum and
> reindex the whole table. Okay so far, I have expected this. But this
> wasn't necessary with the second option, nevertheless, fetching some
> rows showed, that the value of the new column was my default value.

The latter only affects newly inserted rows, changing the default does
not affect any existing rows. If it does, please provide examples.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.